Simple count()/group by question

[Joseph Santaniello]

Hello all,

I have a table with among other things a timestamp field. To get the
count of records per hour I do:

select
count(*) as c,
date_trunc('hour', timestmp) as h
from
mytable
where
date_trunc('day', timestmp) = date_trunc('day', now())
group by h;

Which works, but it only returns a count for hours that have entries.
I'd like it to return count 0 as well for the hours that have no entries.
In other words I'd like my result set to have 24 rows no matter what.

Any suggestions?

Thanks,

Joseph

[Jeff Boes]

Joseph Santaniello wrote:

Hello all,

I have a table with among other things a timestamp field. To get the
count of records per hour I do:

select
count(*) as c,
date_trunc('hour', timestmp) as h
from
mytable
where
date_trunc('day', timestmp) = date_trunc('day', now())
group by h;

Which works, but it only returns a count for hours that have entries.
I'd like it to return count 0 as well for the hours that have no entries.
In other words I'd like my result set to have 24 rows no matter what.

Any suggestions?

I did something similar by left-joining the summary with a view that I
created that looks something like this:

CREATE VIEW "hours_in_past_24" AS
SELECT "timestamp"(date_trunc('hour',now())) as the_hour, 0 as
hours_ago
UNION SELECT "timestamp"(date_trunc('hour',now() - "interval"('1
hours'))), 1
UNION ...
SELECT "timestamp"(date_trunc('hour',now() - "interval"('23 hours'))), 23
ORDER BY 1;

Now you do something like:

select count(*) as c, date_trunc('hour', timestmp) as h
from hours_in_past_24
left join my_table on (the_hour = h)
etc.
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax [phone]
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise