So, I want to show which employees (names) have subordinates and how many subordinates they have (I was using self-join). It works fine if I use count and group by looking at the "manager_id" colum which tells us who the employees boss is. But as soon as I try to add to show the names of my selected bosses (not just their id), it messes up the count, saying that they all have just one subordinate.
I tried using group by, but it just tells me that they all have 1 subordinate.
`select e.first_name,e.last_name,count(e.manager_id) as 'Broj zaposlenika'
from sales.staffs e left join sales.staffs m
on m.staff_id=e.manager_id
where e.staff_id in (select e.manager_id from sales.staffs e left join sales.staffs m
on m.staff_id=e.manager_id)
group by e.manager_id, e.last_name,e.first_name;`
This one displays the correct number of employees, but as soon as I try putting the names in again, the count (number of emplyees gets messed up).
select e.manager_id,count(e.manager_id) as 'Broj zaposlenika' from sales.staffs e left join sales.staffs m on m.staff_id=e.manager_id group by e.manager_id
The issue in the provided query is that it is grouping by employee name(e.first_name, e.last_time). But it should be grouping by manager name(m.first_name, m.last_time).
Change the group by condition to the following (e.manager_id, m.first_name, m.last_time) will make it work.
Here is the query:
select
m.first_name,
m.last_name,
count(e.manager_id) as 'Broj zaposlenika'
from
sales.staffs e
left join
sales.staffs m
on
m.staff_id=e.manager_id
group by
e.manager_id,
m.first_name,
m.last_name