SQL - Show months without data using group by

Question

I've been searching without success for a way to list the data of all months, on format YEAR-MONTH, between two dates in a report.

I have this query that work well but doesn't show the months when doesn't exist data.

SELECT  DATE_FORMAT(FROM_UNIXTIME(bt.date_submitted),'%Y-%m') AS month, count(*) as the_count
        FROM bug_table bt
        WHERE bt.category_id=2 
        AND  bt.date_submitted BETWEEN " . db_prepare_string( $db_datetimes['start'] ) . "
        AND " . db_prepare_string( $db_datetimes['finish'] ) . "
        GROUP BY month
        ORDER BY month ASC

The date_submitted is a UNIX_TIMESTAMP and $db_datetimes is a associative array on php that store the dates chosen by the user.

For instance, I want a list all months between 2016-10-01 and 2017-03-20. I got :

 month   | the_count
 2017-03       7
 2017-02       5
 2017-01       2
 2016-12      10

But I would like as below, including zero on months that have any record:

 month   | the_count
 2017-03       7
 2017-02       5
 2017-01       2
 2016-12      10
 2016-11       0
 2016-10       0

Sincerely I prefer to solve this problem on SQL, but any ideas using SQL or php will be welcome!

score 0 · Answer 1 · answered Mar 20 '17 at 18:11

0

You can create a table with all months and do a left join from that table to your main table. And then replace NULLs with 0.

answered Mar 20 '17 at 18:11

Jon Ekiz

1,002
6
13

score 0 · Answer 2 · answered Mar 20 '17 at 18:14

0

Have you tried to join them on the dates FROM table1 LEFT OUTER JOIN date ON table1.date=table2.date

answered Mar 20 '17 at 18:14

mrcool

63
5

score 0 · Answer 3 · edited May 23 '17 at 12:10

You can go ahead with one of the following approaches:

Create a list of month-year values dynamically (using a query) and LEFT JOIN result of that query to the one you are using. An example SO answer is here.
Create a calenar table and populate it with dates. You can then use the same LEFT JOIN with calendar and get the values.
Handle this logic in service/business layer of your application (i.e.create an array of month-year and show the values on User Interface based on results retrieved from db).

I would recommend using third approach as it's the cleanest one and won't add complexity to your db or service layer.

SQL - Show months without data using group by

3 Answers3