SQL Query List Those that are higher than average

Question

I have 2 tables in the following format:

• employee (employeeID, EmployeeName, DepartmentID)
• departments (DepartmentID, DeptName)

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

Answer 1

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

Answer 2

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