Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite
1

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 |
share|improve this question
 
Your GROUP BY start_at groups by the original timestamp, not by the date-aggregated timestamp –  wildplasser yesterday

1 Answer

up vote 1 down vote accepted

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.