Listing the number of properties handled by each staff member

Question

Listing the first and last name and the count of properties they own.

Need to also include staff members who do not own any properties but count would be 0.

Here I have the staff members and properties linked with staffNo.

This query displays only the staffs who own a property and how many they own but is not displaying the staffs who do not own any properties

SELECT S.branchNo
     , fName
     , lName
     , S.staffNo
     , COUNT(*) AS "count" 
  FROM Staff S
     , PropertyForRent P 
 WHERE S.staffNo = P.staffNo 
 GROUP 
    BY S.branchNo
     , S.staffNo 
 ORDER 
    BY S.staffNo;

move away from implicit join – Madhur Bhaiya Sep 23 '18 at 15:47 — Madhur Bhaiya, Sep 23 '18 at 15:47

score 1 · Answer 1 · answered Sep 23 '18 at 15:47

If you need to include staff members that have not properties, then think left join. Do you know what that is? You are using implicit joins. You need to learn proper, explicit, standard join syntax. It is more powerful, and it does what you want.

SELECT s.fName, s.lName, COUNT(P.staffNo) AS "count
FROM Staff S LEFT JOIN
     PropertyForRent P 
     ON S.staffNo = P.staffNo
GROUP BY fName, lName
ORDER BY S.staffNo;

You should not include extraneous columns in the SELECT. If you want the staff number or other columns, then also include them in the GROUP BY.

score 1 · Accepted Answer · answered Sep 23 '18 at 15:48

Don't use implicit joins. Instead, use Explicit Join based syntax.
To get all the staff members, event without any property, you need to use Left Join (with Staff table as the leftmost table). This allows all the rows of Staff table to come in result (even if there is not matching entry in the PropertyForRent table)

Use the following query:

SELECT S.branchNo, fName, lName, S.staffNo, 
       IFNULL(COUNT(*),0) AS "count" 
FROM Staff S 
LEFT JOIN PropertyForRent P ON S.staffNo = P.staffNo 
GROUP BY S.branchNo, S.staffNo 
ORDER BY S.staffNo

Listing the number of properties handled by each staff member

2 Answers2