By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,302 Members | 1,280 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,302 IT Pros & Developers. It's quick & easy.

Simple count()/group by question

P: n/a
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
Share this Question
Share on Google+
1 Reply

P: n/a
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.