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

user_5677517 0
Viewed 1180

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 |

mysql-workbench mysql sql javascript
5 Answers

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

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

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

user_14476750

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.

user_60039010

You need to start with a list of months. This can be accomplished 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.

user_11440350
Related