470,862 Members | 1,855 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Group Query and want to include null values

sks
Hi,

I have a table which logs for sake of argument page hits:

id, timestamp, ipaddress

Now, I'm running a query to get an activity report and I want to group
together all the hits from a certain time period. Let's say a month.

I can do this with this query:

select count(*), FROM_UNIXTIME(timestamp,\"%u%y\") as d from tablename group
by d

My question is, if there are no rows for a certain time period, then the
query just misses that out. So I might get a result set something like this:

100, Jan
104, Feb
56, Apr

But I want the query to include all months, or all days, or whatever the
period is I'm querying for even if the count is zero. I want the above to be

100, Jan
104, Feb
0, Mar
56, Apr
Jul 20 '05 #1
1 2467
"sks" <sk******************@tek-removeme-spam-catcherheads.co.uk> wrote in
message
select count(*), FROM_UNIXTIME(timestamp,\"%u%y\") as d from tablename group by d

My question is, if there are no rows for a certain time period, then the
query just misses that out. So I might get a result set something like this:
100, Jan
104, Feb
56, Apr
I don't think GROUP BY lets you do this because it doesn't know about
records which don't exist. If you have a table of possible time periods
(like the year + month), then you can left outer join to your activities
table 'd' above. Your aggregation function should not be count(*), but
maybe sum(case ...), or maybe something else if you can find it.

But I want the query to include all months, or all days, or whatever the
period is I'm querying for even if the count is zero. I want the above to be
100, Jan
104, Feb
0, Mar
56, Apr


Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Laphan | last post: by
9 posts views Thread by cyrus.kapadia | last post: by
3 posts views Thread by brendan_gallagher_2001 | last post: by
5 posts views Thread by Mike Nolan | last post: by
2 posts views Thread by M | last post: by
10 posts views Thread by Rudolf Bargholz | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.