How to count rows each month of each year even a month is not present

Question

I'm trying to count the rows of each month of each year (there're a few years in a table) without skipping the empty month. Saying my table has some month without the data in it. Like 1,2,5,6,7,11,12 (3,4,8,9,10 is missing). But I don't want the output skip it - just put 0 instead.

I come up with this code but it skipping the empty record.

SELECT
    YEAR(`log_datetime`) as year,MONTH(`log_datetime`) AS mon,
    count(log_id) AS count
FROM
    log_db
GROUP BY
    MONTH (`log_datetime`),
    YEAR (`log_datetime`)
ORDER BY
    YEAR (`log_datetime`) DESC,
    MONTH (`log_datetime`) DESC

What I expect is:

+------+----+-----+
| 2015 | 12 | 100 |
+------+----+-----+
| 2015 | 11 | 120 |
+------+----+-----+
| 2015 | 10 |  0  |
+------+----+-----+
| 2015 | 09 |  0  |
+------+----+-----+
| 2015 | 08 |  0  |
+------+----+-----+
| 2015 | 07 | 123 |
+------+----+-----+
| 2015 | 06 | 200 |
+------+----+-----+
| 2015 | 05 | 250 |
+------+----+-----+
| 2015 | 04 |  0  |
+------+----+-----+
| 2015 | 03 |  0  |
+------+----+-----+
| 2015 | 02 | 211 |
+------+----+-----+
| 2015 | 01 | 200 |
+------+----+-----+

You need a calendar or tally table as the base table for your query. Also, you really should only tag the DBMS you are using. mysql <> sql server. — Sean Lange, Jan 04 '16 at 16:54
Sorry, I don't understand the idea. Could you please give me a deeper explanation? — Wilf, Jan 04 '16 at 17:03
sql cannot pad missing data in the table. You need to create a table with all the `YEAR` and `MONTH` in it. The use that table as `LEFT JOIN` . — minatverma, Jan 04 '16 at 17:10
Just handle the missing results in PHP - with a simple loop. — Strawberry, Jan 04 '16 at 17:14

score 1 · Answer 1 · edited May 23 '17 at 12:16

1

You need a calendar table to do this.

SELECT c.years,
       c.months, 
       Count(log_id) AS count 
FROM   calendar_table C 
       LEFT OUTER JOIN log_db l 
                    ON c.months = Month (`log_datetime`) 
                       AND c.years = Year (`log_datetime`) 
GROUP  BY c.years, 
          c.months 
ORDER  BY c.months DESC, 
          c.years DESC

Refer the below links to generate calendar table

edited May 23 '17 at 12:16

Community

1
1

answered Jan 04 '16 at 17:08

Pரதீப்

91,748
19
131
172

I created a `calendar_table` and run your code. But the server returns nothing. No error nor result :( – Wilf Jan 04 '16 at 17:37
The code is exactly as you gave me. But I changed some variables name according to the table column. – Wilf Jan 04 '16 at 18:04
@Wilf - Did you populate calendar table with data – Pரதீப் Jan 04 '16 at 18:05
When you have data in `calendar` table am sure the query will return data unless you put some `log_db` table filter in where condition – Pரதீப் Jan 04 '16 at 18:14
It works but it says `Showing rows 0 - 29 ( 372 total, Query took 54.3462 sec)`. Any way to make it faster? – Wilf Jan 04 '16 at 18:21
Filter the calendar table between the date range you need to show – Pரதீப் Jan 04 '16 at 18:35

score 0 · Answer 2 · answered Jan 04 '16 at 17:18

0

Create a calendar table and do a left join with log_db table.

answered Jan 04 '16 at 17:18

Adarsh Gangadharan

653
4
10

How to count rows each month of each year even a month is not present

2 Answers2