Clever Geek Handbook

List of SQL Queries Above Average

I have 2 tables in the following format:

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

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

im looking for results in the following format:

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


source to share


2 answers


Since an employee can only be in one department, the average number of employees is just the number of employees compared to 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))
+1


source 


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

As Oracle

you can use a more elegant analytic functions:



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
+2


source






More articles:


All Articles