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

Group Query and want to include null values

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


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