Mysql to select month-wise record even if data not exist

mysql

Solution

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.

Problem

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?

Original source