CODEWITHSUNDEEP
sqloracle-databaseplsql
lukemh
lukemh

Reputation: 5323

SQL Query List Those that are higher than average

I have 2 tables in the following format:

How many employees there are who work in each of the departments that have more employees than the average number of employees in a department.

im looking to the results in the following format:

Dept Name   | Num of Emp
engineering | 10
science     | 15

Upvotes: 2

Views: 2618

Answers (2)

Mikeb
Mikeb

Reputation: 6361

since an employee can be in only one department, the average number of employees is just the total # of employees over the total number of departments. So how about:

SELECT    dept.name, COUNT(emp.id) AS employeeCount
FROM         emp INNER JOIN dept ON emp.deptId = dept.id
GROUP BY dept.name
HAVING      (COUNT(emp.id) >
(SELECT  COUNT(*) FROM emp) /
(SELECT     COUNT(*) FROM dept))

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425471

SELECT  deptName, cnt
FROM    (
        SELECT  departmentID, COUNT(*) AS cnt
        FROM    employee
        GROUP BY
                departmentID
        HAVING  COUNT(*) >=
                (
                SELECT  AVG(cnt)
                FROM    (
                        SELECT  COUNT(*) AS cnt
                        FROM    employee
                        GROUP BY
                                departmentID
                        )
                )
        ) e
JOIN    departments d
ON      d.departmentID = e.departmentID

In Oracle, you can use analytic functions which are more elegant:

SELECT  DeptName, cnt
FROM    (
        SELECT  q.*, AVG(cnt) OVER() AS acnt
        FROM    (
                SELECT  departmentID, COUNT(*) AS cnt
                FROM    employee
                GROUP BY
                        departmentID
                ) q
        ) e
JOIN    departments d
ON      d.departmentID = e.departmentID
WHERE   cnt >= acnt

Upvotes: 2

Related Questions