Postgres - Partial Column In Select/group By - Column Must Appear In The Group By Clause Or Be Used In An Aggregate Function

Both the following two statements produce an error in Postgres: SELECT substring(start_time,1,8) AS date, count(*) as total from cdrs group by date; SELECT substring(start_time,1,8

Solution 1:

Just to summarise, error

column "cdrs.start_time" must appear in the GROUP BY clause or be used in an aggregate function

was caused (in this case) by ORDER BY start_time clause. Full statement needed to be either:

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);

or

SELECT substring(start_time,1,8) ASdate, count(*) as total FROM cdrs GROUPBYdateORDERBYdate;

Solution 2:

Two simple things you might try:

1. Upgrade to postgres 8.4.1 Both queries Work Just Fine For Me(tm) under pg841

2. Group by ordinal position That is, GROUP BY 1 in this case.