How to get count 0 if data is not present in database using mySQL

Question

I'm showing up monthly user registered on my app. For that, I have used the below query, which is working fine. But with this query, if no user registered in the month of June then no data is there against June. I want a row with month June and all other information set to 0. Can anyone please help me out with this?

SELECT Month(createdon), count(*) as users,COUNT(if(roleid=1,1,NULL)) as instructor, COUNT(if(roleid=2,1,NULL)) as student FROM user_profile where Year(createdon) = Year(Now()) group by MONTH(createdon);

I am getting the output as:

Month(created on) | users | instructor | student | 3 | 4 | 3 | 1 | 4 | 7 | 5 | 2 |

Here, Month 3 and 4 corresponds to March and April respectively.

But the actual output is:

Month(created on) | users | instructor | student | 1 | 0 | 0 | 0 | 2 | 0 | 0 | 0 | 3 | 4 | 3 | 1 | 4 | 7 | 5 | 2 | 5 | 0 | 0 | 0 | 6 | 0 | 0 | 0 |

Answer 1

You can try this:

SELECT MONTH(createdon) AS month, COUNT(*) AS users, COUNT(IF(roleid=1,1,NULL)) AS instructor, COUNT(IF(roleid=2,1,NULL)) AS student
FROM user_profile
WHERE (YEAR(createdon) = YEAR(NOW()))
GROUP BY MONTH(createdon)
UNION
SELECT M.month, 0, 0, 0
FROM (SELECT 1 AS month UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) AS M
WHERE (M.month <= MONTH(NOW()))
  AND (NOT EXISTS (SELECT * FROM user_profile WHERE (MONTH(createdon) = M.month)));

Answer 2

I had the same experience So just use

SELECT IF( EXISTS (SELECT * FROM user_profile WHERE MONTH(createdon)= 'yourmonth')
  ,SELECT 
     Month(createdon),
     COUNT(*) AS users,
     COUNT(if(roleid=1,1,NULL)) AS instructor, 
     COUNT(if(roleid=2,1,NULL)) AS student 
  FROM user_profile 
  WHERE Year(createdon) = Year(Now()) 
  Group BY MONTH(createdon)
,0)