Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL: filling empty fields with zeroes when using GROUP BY

Tags:

mysql

group-by

I've got MySQL table

CREATE TABLE cms_webstat (
    ID int NOT NULL auto_increment PRIMARY KEY,
    TIMESTAMP_X timestamp DEFAULT CURRENT_TIMESTAMP,
    # ... some other fields ...
)

which contains statistics about site visitors.
For getting visits per hour I use

SELECT
    hour(TIMESTAMP_X) as HOUR
    , count(*) AS HOUR_STAT
FROM cms_webstat
GROUP BY HOUR
ORDER BY HOUR DESC

which gives me

| HOUR | HOUR_STAT |
|  24  |    15     |
|  23  |    12     |
|  22  |    9      |
|  20  |    3      |
|  18  |    2      |
|  15  |    1      |
|  12  |    3      |
|   9  |    1      |
|   3  |    5      |
|   2  |    7      |
|   1  |    9      |
|   0  |    12     |

And I'd like to get following:

| HOUR | HOUR_STAT |
|  24  |    15     |
|  23  |    12     |
|  22  |    9      |
|  21  |    0      |
|  20  |    3      |
|  19  |    0      |
|  18  |    2      |
|  17  |    0      |
|  16  |    0      |
|  15  |    1      |
|  14  |    0      |
|  13  |    0      |
|  12  |    3      |
|  11  |    0      |
|  10  |    0      |
|   9  |    1      |
|   8  |    0      |
|   7  |    0      |
|   6  |    0      |
|   5  |    0      |
|   4  |    0      |
|   3  |    5      |
|   2  |    7      |
|   1  |    9      |
|   0  |    12     |

How should I modify the query to get such result (with one mysql query, without creating temporary tables)?
Is it possible to get such result with one MySQL query?

like image 724
foreline Avatar asked Mar 19 '10 18:03

foreline


2 Answers

This is just the 'why it is not returning` part. Marcus' answer covers the 'how to' part.

The SQL

SELECT 
    hour(TIMESTAMP_X) as HOUR 
    , count(*) AS HOUR_STAT 
FROM cms_webstat 
GROUP BY HOUR 
ORDER BY HOUR DESC 

gets the count of the records per hour, for the timestamps present in the table

It does not give the details of what is not present in the table. Since there is no recors for the timestamp corresponding to the hour 8 (from your example) the SQL does not return any records.

like image 181
Nivas Avatar answered Sep 25 '22 02:09

Nivas


Create another table with a single column,

CREATE TABLE hours_list (
    hour int NOT NULL PRIMARY KEY
)

Fill it with all 24 hours.

Then do a join on that table to fill in the zeroes.

SELECT
    hs.hour as HOUR, COUNT(ws.ID) AS HOUR_STAT
FROM hours_list hs 
LEFT JOIN cms_webstat ws ON hs.hour = hour(ws.TIMESTAMP_X)
GROUP BY hs.hour
ORDER BY hs.hour DESC
like image 25
Marcus Adams Avatar answered Sep 26 '22 02:09

Marcus Adams