Using distinct dates and aggregate functions with postgres

Question

I'm trying to get a set of averages for each month. I'm using DATE_TRUNC to convert the date time into month year only but I still can't get the distinct DATE to work. Seems to ignore it entirely.

I only want 6 rows, one for each month in the RANGE, but instead I get 1000s.

SELECT DISTINCT(date_trunc('month', event_logs.start_at)) AS start_at, 
AVG(event_logs.head_count) as head_count FROM "event_logs" 
WHERE ("event_logs"."state" IN ('completed')) AND ("event_logs"."start_at" 
BETWEEN '2013-05-09 10:12:58.824846' AND '2013-11-09') 
GROUP BY start_at

Also tried throwing in DISTINCT(DATE(date_trunc()) but that didn't do anything? Is there something special with DATES I'm not seeing?

+----+---------------------+---------------------------+
| id | head_count          | start_at                  |
+----+---------------------+---------------------------+
|    | 17.0                | 2013-06-01 01:00:00 +0100 |#WHY???!?!?!
|    | 15.0                | 2013-06-01 01:00:00 +0100 |#YOU ARE CLONES!
|    | 40.5                | 2013-06-01 01:00:00 +0100 |#NOT DISTINCT!
|    | 32.5                | 2013-10-01 01:00:00 +0100 |
|    | 69.0                | 2013-08-01 01:00:00 +0100 |
|    | 34.9                | 2013-10-01 01:00:00 +0100 |
|    | 9.0                 | 2013-07-01 01:00:00 +0100 |

Your GROUP BY start_at groups by the original timestamp, not by the date-aggregated timestamp — wildplasser
– wildplasser, Commented Nov 9, 2013 at 12:42

Andrey Morozov · Accepted Answer · 2013-11-09 12:44:50Z

5

Try this:

SELECT
date_trunc('month', event_logs.start_at) AS start_at, 
AVG(event_logs.head_count) as head_count 
FROM "event_logs" 
WHERE ("event_logs"."state" IN ('completed')) 
AND ("event_logs"."start_at" 
BETWEEN '2013-05-09 10:12:58.824846' AND '2013-11-09') 
GROUP BY date_trunc('month', event_logs.start_at)

Your problem is in the GROUP BY statement. You need to GROUP BY month instead of date/time value. And you do not need to use DISTINCT with GROUP BY

answered Nov 9, 2013 at 12:44

Andrey Morozov

8,0175 gold badges59 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

Using distinct dates and aggregate functions with postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related