Mysql to select the monthly record, even if the data does not exist

Question

I wrote a query to get a monthly entry in the 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 Result:

 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 the data does not exist. How to do it?

Answer 1

I will not talk about efficiency, since I did not test it against other methods, but without a temp table this looks like a fair way.

  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 create a union based on the date format, you can even reduce the work and load the request.

  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 requests.

Answer 2

You may need a table to store each month record. A temporary 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 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); 

Note that you can put the temp table creation (and populate) into the stored procedure.

I use last_day for simplicity, but you can use any date of the month that you like if you attach it correctly.

Hope this helps