mysql - Get all the rows from the right joined table

Question

I am struggling to get the output in the following format: e.g.

| Total | Roles | Month | Year |
--------------------------------
| 52    | Admin |January| 2012 |
| 0     | Tester|July   | 2012 |
| 41    | HR    |January| 2014 |
| 0     | TL    |June   | 2015 |
| 14    | VP    |March  | 2011 |

I am having two tables (say users and userType), here "type" column in users table is reference to userType table.

I want to get all the rows from right joined table and if there are no values in the left table then it should display "0".

Please view below which I have tried:

SELECT COALESCE(COUNT(u.`userId`),0) AS 'Total',r.`roleName` AS 'Role',COALESCE(MONTH(u.`userOn`),0) AS 'Month', COALESCE(YEAR(u.`userOn`),0) AS 'Year' FROM `users` u RIGHT JOIN `usertype` r ON u.`userRole` = r.`roleId` WHERE YEAR(`userOn`) > 2011 and u.`userRole` = r.`roleId` GROUP by r.`roleName`;

How to get the query for the provided output?

Answer 1

Put your YEAR(userOn) > 2011 condition in ON Clause like below

SELECT COALESCE(COUNT(u.`userId`),0) AS 'Total',r.`roleName` AS 'Role' 
FROM `usertype` r left join `users` u ON u.`userRole` = r.`roleId` 
and YEAR(`userOn`) > 2011
GROUP by r.`roleName`;