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 |

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 |

• javascript
• mysql
• sql
• mysql-workbench

Share Improve this question Follow edited Jun 17, 2019 at 7:52 El.Hum 1,47933 gold badges1515 silver badges2525 bronze badges asked Jun 17, 2019 at 6:33 Ruchi yadavRuchi yadav 24333 silver badges1616 bronze badges

Add a ment  | 

5 Answers 5

Sorted by: Reset to default 2

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)));

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)

You could collect the sorted SQL result and iterate a custom range and insert either the given data or an empty result set.

function getRange(from, to, result) {
    var i, j = 0;
    for (i = from; i <= to; i++) {
        if (result[j] && i === result[j][0]) {
            console.log(...result[j++]);       // slq result
        } else {
            console.log(i, 'empty row');       // row with zeroes
        }
    }
}

getRange(1, 6, [[3, 2, 5], [4, 3, 2]]);

You are not seeing row with 0 value because data for given month is not there in your table and groupby will show data of months that are present.

You can generate a month list for given year -

select  TO_CHAR((current_date - interval '1 month' * a),'MM') AS mm FROM generate_series(1,12,1) AS s(a)

it will generate value like

01
02
03
.
.

Then you can write a subquery with this table and do left join with your user_profile table and you will get data as you have specified.

For more read, go through this link.

You need to start with a list of months. This can be acplished with a derived table:

SELECT m.mon, COUNT(up.createdon) as users,
       SUM(up.roleid = 1) as instructor,  
       SUM(up.roleid = 2) as student
FROM (SELECT 1 as mon UNION ALL
      SELECT 2 as mon UNION ALL
      SELECT 3 as mon UNION ALL
      SELECT 4 as mon UNION ALL
      SELECT 5 as mon UNION ALL
      SELECT 6 as mon 
     ) m LEFT JOIN
     user_profile up
     ON m.mon = MONTH(up.createdon) AND
        YEAR(createdon) = YEAR(Now()) 
GROUP BY m.mon;

If you have a calendar table or numbers table, you can use that instead of the derived table.