sql - find average salary for each department with more than five members

Question

Not quite sure how to get this one. I have a staff table and I need to find the average salary. I know I can use use avg(). But the trick is I need to find the average for departments that have more than 5 staff members. I'm not sure if I should use group by or how to use it. Thanks!

    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));

score 7 · Accepted Answer · edited Feb 22 '12 at 10:38

7

select DEPARTMENT,count(STAFF_ID) as CountStaff, avg(SALARY) as AVGSalary
from STAFF
group by DEPARTMENT
having count(STAFF_ID) > 5

edited Feb 22 '12 at 10:38

dani herrera

48,760
8
117
177

answered Feb 22 '12 at 08:29

Vikram

8,235
33
47

If i have something like this: List the names of all boss(es) who have at least 3 staffs. Should i use "having" also? – user1225281 Feb 22 '12 at 08:58

sql - find average salary for each department with more than five members

1 Answers1

Linked