469,954 Members | 1,780 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,954 developers. It's quick & easy.

Simple count()/group by question

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
Jul 19 '05 #1
1 7315
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

Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Jim Clark | last post: by
18 posts views Thread by Geoff Cox | last post: by
2 posts views Thread by DKode | last post: by
6 posts views Thread by Eddie Smit | last post: by
7 posts views Thread by Riley DeWiley | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.