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

sql having average
user1225281 picture user1225281 · Feb 22, 2012 · Viewed 57.9k times · Source

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

Vikram picture Vikram · Feb 22, 2012 
select DEPARTMENT,count(STAFF_ID) as CountStaff, avg(SALARY) as AVGSalary
from STAFF
group by DEPARTMENT
having count(STAFF_ID) > 5

Related questions

Oracle ORDER BY with rownum or HAVING >= ALL

My database teacher asked me to write (on Oracle Server) a query: select the groupid with the highest score average for year 2010 I wrote: SELECT * FROM ( SELECT groupid, AVG(score) average FROM points WHERE yr = 2010 AND score IS NOT NULL …

Read More
SQL query for finding records where count > 1

I have a table named PAYMENT. Within this table I have a user ID, an account number, a ZIP code and a date. I would like to find all records for all users that have more than one payment per …

Read More
What is the difference between HAVING and WHERE in SQL?

What is the difference between HAVING and WHERE in an SQL SELECT statement? EDIT: I have marked Steven's answer as the correct one as it contained the key bit of information on the link: When GROUP BY is not used, …

Read More