sql - find average salary for each department with

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

Answer 1

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