sql counting rows

Question

I have the following table in MySQL...

CREATE TABLE `visits` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `visit` datetime NOT NULL default '0000-00-00 00:00:00',
  `bulletin` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
)

Where "visit" is the time a visit occurs, and "bulletin" is the article requested at that time.

I want to know what times are popular with my users, so I have the following query...

SELECT 
    count(*) as c, 
    Date_format (visit, '%l%p, %e %b %Y') as e
FROM 
    `visits`
WHERE 1
AND 
    date(visit) > date(now() - interval 2 day)
group by e
order by visit desc

Which returns...

c   e  
4   12PM, 27 Oct 2009  
9   11AM, 27 Oct 2009  
5   10AM, 27 Oct 2009  
2   9AM, 27 Oct 2009  
3   4PM, 26 Oct 2009  
6   3PM, 26 Oct 2009  
16  2PM, 26 Oct 2009

So far so good.

But how do I get the query to return "0" for count and the time for the missing hours? for example, I'd like the results to look something like...

c   e  
4   12PM, 27 Oct 2009  
9   11AM, 27 Oct 2009  
5   10AM, 27 Oct 2009  
2   9AM, 27 Oct 2009
0   8AM, 27 Oct 2009
0   7AM, 27 Oct 2009
0   6AM, 27 Oct 2009
0   5AM, 27 Oct 2009
0   4AM, 27 Oct 2009
0   3AM, 27 Oct 2009
0   2AM, 27 Oct 2009
0   1AM, 27 Oct 2009
0   12am, 27 Oct 2009
0   11pm, 26 Oct 2009
0   10pm, 26 Oct 2009
0   9pm, 26 Oct 2009
0   8pm, 26 Oct 2009
0   7pm, 26 Oct 2009    
0   6pm, 26 Oct 2009  
0   5pm, 26 Oct 2009
3   4PM, 26 Oct 2009  
6   3PM, 26 Oct 2009  
16  2PM, 26 Oct 2009

I could fudge this in the output code when I print the results, but I'd like the query to provide it for me, if possible.

Answer 1

You probably have to create fake dates in your query to accomplish this... simply fill an array with the 24 hours of the day, then only change the 0 values in the query rows loop.

Answer 2

You should be selecting from a generated data set of all the dates & times, left joined to the table 'visits'. I don't know MySql very well, so I can't give you an example of how to generate the set of dates and times, but something like:

SELECT 
    count(*) as c, 
    Date_format (d.DateTime, '%l%p, %e %b %Y') as e
FROM 
    GetDateSetFunction() d
LEFT JOIN
    `visits` v
    ON Date_format(d.DateTime, '%l%p, %e %b %Y') = Date_format (v.visit, '%l%p, %e %b %Y') 
WHERE 1
AND 
    date(d) > date(now() - interval 2 day)
group by e
order by d.DateTime desc

Answer 3

You should be able to do a union for all the sums.

select count(*) as c, Date_format (visit, '%l%p, %e %b %Y') as e
from table where 
date(visit) > date(now() - interval 2 day) 
and date(visit) < date(now() - interval 2 day + 1 hour)
group by e
union
select count(*) as c, Date_format (visit, '%l%p, %e %b %Y') as e
from table where
date(visit) > date(now() - interval 2 day + 1 hour) 
and date(visit) < date(now() - interval 2 day + 2 hour)
group by e
... #etc
union
select count(*) as c, Date_format (visit, '%l%p, %e %b %Y') as e
from table where
date(visit) > date(now() - interval 2 day + 47 hour) 
and date(visit) < date(now() - interval 2 day + 48 hour)
group by e;

The idea is you grab each hour separately, grouping them together. When there are no results for a particular time, you still get the 0 count.

I am not very familiar with the syntax details of mysql, so I've put this together as psuedo-sql. I've copied most syntax from your code, but don't know if I've missed something. I have not tested this code.

Answer 4

1. Create a table of numbers:

   CREATE TABLE `NUMBERS` (
     `num` int(11) NOT NULL auto_increment,
     PRIMARY KEY  (`num`)
   )
   

2. Populate the table:

   INSERT INTO NUMBERS VALUES (NULL)
   

   Based on your example, you'd need to run the statement ~36 times.

3. Use the following:

      SELECT IFNULL(vd.num_visits, 0),
             DATE_FORMAT(s.gdate, '%l%p, %e %b %Y')
        FROM (SELECT DATE_ADD(@min_date, INTERVAL n.num-1 HOUR) 'gdate'
                FROM NUMBERS n
               WHERE DATE_ADD(@min_date, INTERVAL n.num-1 HOUR) <= @max_date ) s
   LEFT JOIN (SELECT v.visits,
                     COUNT(*) 'num_visits'
                FROM visits v
            GROUP BY v.visits) vd ON vd.visits = s.gdate
    ORDER BY s.gdate DESC
   

The numbers table is an old trick, used when you can't recursively generate a list of values.

If you want to use based on min/max of the VISITS table, use:

FROM (SELECT DATE_ADD(@min_date, INTERVAL n.num-1 HOUR) 'gdate'
        FROM NUMBERS n
        JOIN (SELECT MIN(t.visits) 'minv',
                     MAX(t.visits) 'maxv'
                FROM visits t) t
       WHERE DATE_ADD(t.minv, INTERVAL n.num-1 HOUR) <= t.maxv ) s