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

pyrocumulus · Accepted Answer · 2012-11-07 12:57:59Z

46

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.

answered Nov 7, 2012 at 12:57

pyrocumulus

9,3402 gold badges46 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SingleNegationElimination · Accepted Answer · 2012-11-07 13:09:21Z

10

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)

answered Nov 7, 2012 at 13:09

SingleNegationElimination

157k35 gold badges269 silver badges306 bronze badges

1 Comment

sequielo Over a year ago

min()in the ORDER BY saved my day!

Collectives™ on Stack Overflow

group by date aggregate function in postgresql

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related