SQL Exercise: Departments with more employees than average

SQL subqueries on employee Database: Exercise-44 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

44. From the following table, write a SQL query to find those departments where more than average number of employees works. Return department name.

Sample table: employees

Sample table: department

Sample Solution:

SELECT d.dep_name
FROM department d,
     employees e
WHERE e.dep_id = d.dep_id
GROUP BY d.dep_name
HAVING count(*) >
  (SELECT AVG (mycount)
   FROM
     (SELECT COUNT(*) mycount
      FROM employees
      GROUP BY dep_id) a);

Sample Output:

 dep_name
-----------
 MARKETING
 AUDIT
(2 rows)

Explanation:

The said query in SQL that retrieves the names of departments that have more employees than the average number of employees per department.

The query starts by joining the department and employees tables using the dep_id column to match the department of each employee. Then, it groups the result by dep_name and applies a HAVING clause to filter the results.

The HAVING clause compares the count of employees in each department with the average count of employees per department. The subquery that counts the number of employees per department and calculates the average of those counts.

Practice Online

Structure of employee Database:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Employees who joined in the company on the same date.
Next SQL Exercise: List the name of the managers with the most employees.