Mysql query to get count for the last 6 months including with the count 0

Question

I need to get a list that contains a count persons number per month in last the 6 months, How can I force query to give me the months with count = 0? Like this:

table A 
-----------------------------
id |    name    |  created   | 
-----------------------------
1  |    mark    |'2014-10-01'|
2  |    peter   |'2014-10-02'|
3  |    Roger   |'2014-10-03'|
4  |    John    |'2014-09-02'|
5  |    moyes   |'2014-09-03'|
6  |    david   |'2014-08-04'|

The result I need to get:

  ----------------------------
  MONTHS  |  YEARS  | TOTAL  |
  ----------------------------
  January |  2015   |  0     |
  December|  2014   |  0     |
  November|  2014   |  0     |
  October |  2014   |  3     |
 September|  2014   |  2     |
  August  |  2014   |  1     |

Query I've tried so far: http://sqlfiddle.com/#!2/ef54ce/6

But you have only three months in your table. Where are you going to get the rest of months? There is neither December, nor July. Why do you think it must return these months? — Alex, Nov 30 '14 at 13:00
You cannot select data that is not present in your database. You have to simulate it in some way. — Strawberry, Nov 30 '14 at 13:04
@GordonLinoff I just fixed the date (2015-01-20), so the last 6 months (aug 2014, sept 2014, oct 2014, nov 2014, dec 2014, Jan 2015) — muhnizar, Nov 30 '14 at 13:12
@Alex other 3 months (Jan 2015, Dec 2014, Nov 2014) not present in database but still in interval the last 6 months so I need to include the months even the count is 0. — muhnizar, Nov 30 '14 at 13:16
I am afraid it's impossible. You want mysql to be able to draw some rows for you. If there aren't such rows how mysql will know what you wonna get? It looks strange. — Alex, Nov 30 '14 at 13:23
@muhnizar - Look up something called a Calendar Table; essentially, it's a table with a row for every single day on a calendar, usually with a _lot_ of other information extracted out and indexed (almost as many as you want). Like say, columns for year/month/day-of-month... You can then use them to get date ranges for queries, as [demonstrated in this PostGreSQL answer](http://stackoverflow.com/questions/24156202/postgresql-group-month-wise-with-missing-values/24161958#24161958). Hands down the most useful dimension/analysis table you can have. — Clockwork-Muse, Nov 30 '14 at 15:29

londomloto · Answer 1 · 2014-11-30T15:00:43.423

maybe, you have to provide dummy table that contains initial data report. then, union with actual data. to avoid duplicate data u need to use aggregation

SELECT
    m, y, MAX(v) v
FROM
    (
        SELECT 
            DATE_FORMAT('2014-01-01', '%M') as m,
            DATE_FORMAT('2014-01-01', '%Y') as y,
            0 as v
        UNION

        SELECT 
            DATE_FORMAT('2014-02-01', '%M') as m,
            DATE_FORMAT('2014-02-01', '%Y') as y,
            0 as v
        UNION

        .... until December ...

        UNION
        SELECT 
            DATE_FORMAT(created, '%M') as m,
            DATE_FORMAT(created, '%Y') as y,
            COUNT(1) as v
        FROM your_table
        GROUP BY DATE_FORMAT(created, '%Y-%M')
    ) t
GROUP BY y, m
... adjust result ...

Mysql query to get count for the last 6 months including with the count 0

1 Answers1