MySQL left join with a single table

Using the following query to get a hourly breakdown of transactions

SELECT hour(Stamp) AS Hour, count(1) AS Count FROM Transactions GROUP by 1 WITH ROLLUP;

Results in the following output:

+------+-------+
| Hour | Count |
+------+-------+
|    0 |   269 |
|    1 |   342 |
|    2 |   319 |
|    3 |   284 |
|    4 |   235 |
|    5 |   174 |
|    6 |    91 |
|    7 |    54 |
|    8 |    31 |
|    9 |    21 |开发者_如何学Python
|   10 |    21 |
|   11 |     1 |
| NULL |  1842 |
+------+-------+

I would like to display the hours with 0 transactions (e.g. in this example, every hour between 12 and 23 would show '0'). What would be the simplest way to do this?

try something like this (the -1 hour_id is for the rollup total):

drop table if exists hours;
create table hours(hour_id tinyint primary key) engine=innodb;

insert into hours (hour_id) values 
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),
(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(0),(-1);

select
 h.hour_id,
 if(t.counter is null, 0, t.counter) as counter
from
 hours h
left outer join 
(
 select 
    if(hour(stamp) is null, -1, hour(stamp)) as hour_id, 
    count(stamp) as counter
 from 
    transactions group by stamp with rollup
) t
on h.hour_id = t.hour_id;

if you want it by months create a months table 1..12 + -1 etc...

SELECT Temp.Hour, COUNT(1)
FROM (
    SELECT 0  AS Hour UNION
    SELECT 1  UNION
    SELECT 2  UNION
    SELECT 3  UNION
    SELECT 4  UNION
    SELECT 5  UNION
    SELECT 6  UNION
    SELECT 7  UNION
    SELECT 8  UNION
    SELECT 9  UNION
    SELECT 10 UNION
    SELECT 11 UNION
    SELECT 12 UNION
    SELECT 13 UNION
    SELECT 14 UNION
    SELECT 15 UNION
    SELECT 16 UNION
    SELECT 17 UNION
    SELECT 18 UNION
    SELECT 19 UNION
    SELECT 20 UNION
    SELECT 21 UNION
    SELECT 22 UNION
    SELECT 23
) AS Temp
LEFT JOIN Transactions
ON Temp.Hour = HOUR(Transactions.Stamp)
GROUP BY Temp.Hour
ORDER BY Temp.Hour