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

Siemel Naran

"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