current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I need to get the count of the distinct names per hour in one query in PostgreSQL 9.1

The relevant columns(generalized for question) in my table are:

occurred timestamp with time zone and
name character varying(250)

And the table name for the sake of the question is just table

The occurred timestamps will all be within a midnight to midnight(exclusive) range for one day. So far my query looks like:

'SELECT COUNT(DISTINCT ON (name)) FROM table'

It would be nice if I could get the output formatted as a list of 24 integers(one for each hour of the day), the names aren't required to be returned.

share|improve this question
    
Thanks to all those who answered, I found out I had some data problems while testing your scripts, so I'm fixing that, then going to test and accept. –  notbad.jpeg Jul 18 '12 at 16:24

3 Answers 3

up vote 1 down vote accepted

You want this by hour:

select extract(hour from occurred) as hr, count(distinct name)
from table t
group by extract(hour from occurred)
order by 1

This assumes there is data for only one day. Otherwise, hours from different days would be combined. To get around this, you would need to include date information as well.

share|improve this answer
    
is table the table name or is t the table name? –  notbad.jpeg Jul 18 '12 at 16:00
1  
@notbad.jpeg: table is the name and t is the alias –  a_horse_with_no_name Jul 18 '12 at 16:09
    
This one returns the data formatted beautifully! Thanks! –  notbad.jpeg Jul 18 '12 at 22:11
up vote 2 down vote

If I understand correctly what you want, you can write:

SELECT EXTRACT(HOUR FROM occurred),
       COUNT(DISTINCT name)
  FROM ...
 WHERE ...
 GROUP
    BY EXTRACT(HOUR FROM occurred)
 ORDER
    BY EXTRACT(HOUR FROM occurred)
;
share|improve this answer
    
+1 for showing where to put extra parameters and nice capitalization –  notbad.jpeg Jul 18 '12 at 22:15
up vote 2 down vote 
SELECT date_trunc('hour', occurred) AS hour_slice
      ,count(DISTINCT name) AS name_ct
FROM   mytable
GROUP  BY 1
ORDER  BY 1;

DISTINCT ON is a different feature.

date_trunc() gives you a sum for every distinct hour, while EXTRACT sums per hour-of-day over longer periods of time. The two results do not add up, because summing up multiple count(DISTINCT x) is equal or greater than one count(DISTINCT x).

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.