Mysql to select month-wise record even if data not

Question

I wrote a query to get month-wise record in user table as follows

SELECT COUNT( `userID` ) AS total, DATE_FORMAT( `userRegistredDate` , '%b' ) AS
MONTH , YEAR( `userRegistredDate` ) AS year
FROM `users`
GROUP BY DATE_FORMAT( FROM_UNIXTIME( `userRegistredDate` , '%b' ) )

Output:

total      MONTH    year
---------------------------
3           May     2013
2           Jul     2013
--------------------------

Expected Output:

total      MONTH    year
---------------------------
 0          Jan     2013
 0          Feb     2013
 0          Mar     2013
 0          Apr     2013
 3          May     2013
 0          Jun     2013 
 2          Jul     2013
--------------------------

I need to show the record even if data not exist. How to do this?

Answer 1

I won't say much about efficiency as I have not tested it against other methods but without having a temp table this seem a fair way to go.

   SELECT COUNT(u.userID) AS total, m.month
     FROM (
           SELECT 'Jan' AS MONTH
           UNION SELECT 'Feb' AS MONTH
           UNION SELECT 'Mar' AS MONTH
           UNION SELECT 'Apr' AS MONTH
           UNION SELECT 'May' AS MONTH
           UNION SELECT 'Jun' AS MONTH
           UNION SELECT 'Jul' AS MONTH
           UNION SELECT 'Aug' AS MONTH
           UNION SELECT 'Sep' AS MONTH
           UNION SELECT 'Oct' AS MONTH
           UNION SELECT 'Nov' AS MONTH
           UNION SELECT 'Dec' AS MONTH
          ) AS m
LEFT JOIN users u 
ON MONTH(STR_TO_DATE(CONCAT(m.month, ' 2013'),'%M %Y')) = MONTH(u.userRegistredDate)
   AND YEAR(u.userRegistredDate) = '2013'
GROUP BY m.month
ORDER BY 1+1;

If you make the union based on a date format you can even reduce the work and load on the query.

   SELECT COUNT(u.userID) AS total, DATE_FORMAT(merge_date,'%b') AS month, YEAR(m.merge_date) AS year
     FROM (
           SELECT '2013-01-01' AS merge_date
           UNION SELECT '2013-02-01' AS merge_date
           UNION SELECT '2013-03-01' AS merge_date
           UNION SELECT '2013-04-01' AS merge_date
           UNION SELECT '2013-05-01' AS merge_date
           UNION SELECT '2013-06-01' AS merge_date
           UNION SELECT '2013-07-01' AS merge_date
           UNION SELECT '2013-08-01' AS merge_date
           UNION SELECT '2013-09-01' AS merge_date
           UNION SELECT '2013-10-01' AS merge_date
           UNION SELECT '2013-11-01' AS merge_date
           UNION SELECT '2013-12-01' AS merge_date
          ) AS m
LEFT JOIN users u 
       ON MONTH(m.merge_date) = MONTH(u.userRegistredDate)
          AND YEAR(m.merge_date) = YEAR(u.userRegistredDate)
GROUP BY m.merge_date
ORDER BY 1+1;

Live DEMO of both queries.

Answer 2

You may need a table to hold every "month" record. A temp table can do the trick:

drop table if extists temp_months;
create temporary table temp_months
    month date,
    index idx_date(month);
insert into temp_months
    values ('2013-01-31'), ('2013-02-28'), ...

And now, you can left join your data with this newly created temp table:

SELECT 
    COUNT( `userID` ) AS total, 
    DATE_FORMAT( m.month , '%b' ) AS
    MONTH , 
    YEAR( m.month ) AS year
FROM 
    months as m
    left join `users` as u on m.month = last_day(FROM_UNIXTIME(`userRegistredDate`, '%b' )
GROUP BY 
    last_day(m.month);

Notice that you can put the temp table creation (and fill) in a stored procedure.

I use last_day for simplicity, but you are free to use any date in the month that you like, if you join it correctly.

Hope this helps