I want the users(wnr) that are boses(chef) and check how many poeple work for each one.
Example:
user 1 is a boss and is the boss of 3 poeple
user 2 is a boss and is the boss of 4 poeple
user 3 is the boss of user 1 and 2
| wnr(user) | aantal werknemers(amount of employees) |
|---|---|
| 1 | 3 |
| 2 | 4 |
| 3 | 2 |
The table and data I use.
What I tried.
SELECT w.wnr, w.wnaam, COUNT(w.wnr) AS 'aantal werknemers'
FROM `werknemer` AS `w`
GROUP BY w.chef
But that does seem to get the oposide of what I want.
CodePudding user response：
select s.wnr, s.wnaam, count(w.wnr) as aantal
from werknemer s
left join werknemer w on s.chef = w.wnr
group by w.wnr, w.wmaan
Maybe something like that