MySQL: filling empty fields with zeroes when using GROUP BY

Posted by SaltLake on Stack Overflow See other posts from Stack Overflow or by SaltLake
Published on 2010-03-19T18:42:20Z Indexed on 2010/03/23 17:53 UTC
Read the original article Hit count: 255

Filed under:
|
|

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?
Thanks.

© Stack Overflow or respective owner

Related posts about mysql

Related posts about mysql-query