group by date aggregate function in postgresql

Question

I'm getting an error running this query

SELECT date(updated_at), count(updated_at) as total_count 
FROM "persons"
WHERE ("persons"."updated_at" BETWEEN '2012-10-17 00:00:00.000000' AND '2012-11-07     12:25:04.082224') 
GROUP BY date(updated_at)
ORDER BY persons.updated_at DESC

I get the error ERROR: column "persons.updated_at" must appear in the GROUP BY clause or be used in an aggregate function LINE 5: ORDER BY persons.updated_at DESC

This works if I remove the date( function from the group by call, however I'm using the date function because i want to group by date, not datetime

any ideas

Answer 1

At the moment it is unclear what you want Postgres to return. You say it should order by persons.updated_at but you do not retrieve that field from the database.

I think, what you want to do is:

SELECT date(updated_at), count(updated_at) as total_count 
FROM "persons"
WHERE ("persons"."updated_at" BETWEEN '2012-10-17 00:00:00.000000' AND '2012-11-07     12:25:04.082224') 
GROUP BY date(updated_at)
ORDER BY count(updated_at) DESC -- this line changed!

Now you are explicitly telling the DB to sort by the resulting value from the COUNT-aggregate. You could also use: ORDER BY 2 DESC, effectively telling the database to sort by the second column in the resultset. However I highly prefer explicitly stating the column for clarity.

Note that I'm currently unable to test this query, but I do think this should work.

Answer 2

the problem is that, because you are grouping by date(updated_at), the value for updated_at may not be unique, different values of updated_at can return the same value for date(updated_at). You need to tell the database which of the possible values it should use, or alternately use the value returned by the group by, probably one of

SELECT date(updated_at) FROM persons GROUP BY date(updated_at)
ORDER BY date(updated_at)

or

SELECT date(updated_at) FROM persons GROUP BY date(updated_at)
ORDER BY min(updated_at)