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 269.349.9076
Nexcerpt, Inc.
http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise