List all bosses who have at least 3 staff members

Question

How can I query the table below to get the names of all bosses (ie boss_id = staff_id) who have at least 3 staff? Do I use having, group by, ...?

    CREATE TABLE STAFF (STAFF_ID                    CHAR(3),
                        STAFF_NAME              CHAR(20),
                        GENDER                  CHAR(6),
                        DEPARTMENT              CHAR(20),
                        BOSS_ID                 CHAR(3)
                        SALARY                  NUMBER(8,2))

Thanks!

@Bridge: [Not the first one too...](http://stackoverflow.com/questions/9391356/sql-find-average-salary-for-each-department-with-more-than-five-members) — Andriy M, Feb 22 '12 at 10:55
of course i'm trying, i'm just not yet familiar with 'group by', 'having' function.. — user1225281, Feb 22 '12 at 11:02
@user1225281 It's essentially a where clause, but for aggregate functions used with grouping. See http://msdn.microsoft.com/en-us/library/ms180199.aspx for an example. — Bridge, Feb 22 '12 at 12:22

score 2 · Answer 1 · answered Feb 22 '12 at 09:14

I'm using COUNT(*)>3 because I assume, that you mean 3 staff except the boss himself.

SELECT a.staff_name
FROM staff a
INNER JOIN (
  SELECT boss_id,COUNT(*) AS cnt_staff FROM staff GROUP BY boss_id HAVING COUNT(*)>3
) b ON a.boss_id=b.boss_id
WHERE a.staff_id=a.boss_id

score 1 · Answer 2 · edited Feb 22 '12 at 21:04

1

select boss_id, staff_name 
from staff 
group by boss_id, staff_name 
having count(staff_id)>=3

edited Feb 22 '12 at 21:04

rene

41,474
78
114
152

answered Feb 22 '12 at 09:14

mindandmedia

6,800
1
24
33

its at position 2 in your result set. if you just want the staff_name, just leave out the boss_id in the select and the group by. then the staff_name is your only result. – mindandmedia Feb 22 '12 at 09:18
but if in the case where i look for the name of the boss, can i do : select STAFF_ID from staff where STAFF_ID = BOSS_ID group by BOSS_ID having count(STAFF_ID)>=3 – user1225281 Feb 22 '12 at 09:22
@user1225281 no, that's not possible – rabudde Feb 22 '12 at 12:08

score 1 · Answer 3 · answered Feb 22 '12 at 09:24

1

select S.Boss_ID, X.STAFF_NAME
from STAFF S cross apply (Select staff_name from STAFF S2 where S2.staff_id=S.boss_ID) X
group by S.Boss_ID, X.STAFF_NAME
having count(*)>=3

answered Feb 22 '12 at 09:24

Diego

34,802
21
91
134

List all bosses who have at least 3 staff members

3 Answers3