postgres-select/group by-column中的分部列必须出现在group by子句中或在聚合函数中使用

你做了一些你在问题中没有描述的事情,因为你的两个查询都很好。8.5和8.3.8测试:

# create table cdrs (start_time text);
CREATE TABLE

# insert into cdrs (start_time) values ('20090101121212'),('20090101131313'),('20090510040603');
INSERT 0 3

# SELECT substring(start_time,1,8) AS date, count(*) as total from cdrs group by date;
   date   | total
----------+-------
 20090510 |     1
 20090101 |     2
(2 rows)

# SELECT substring(start_time,1,8) AS date, count(*) as total from cdrs group by substring(start_time,1,8);
   date   | total
----------+-------
 20090510 |     1
 20090101 |     2
(2 rows)

总结一下,错误

“cdrs.start_time”列必须出现在group by子句中,或在聚合函数中使用

(在这种情况下)是由 ORDER BY start_time 条款。完整声明必须是:

SELECT substring(start_time,1,8) AS date, count(*) as total FROM cdrs GROUP BY substring(start_time,1,8) ORDER BY substring(start_time,1,8);

或

    SELECT substring(start_time,1,8) AS date, count(*) as total FROM cdrs GROUP BY date ORDER BY date;

您可以尝试两个简单的方法:

1. 升级至Postgres 8.4.1
   两个查询 对我来说工作很好 (tm)根据PG841

2. 按顺序位置分组
   也就是说, GROUP BY 1 在这种情况下。