SQL Exercise: Department average salaries less than averages of all

SQL subqueries on employee Database: Exercise-58 with Solution

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

58. From the following table, write a SQL query to find those departments where the average salary is less than the averages for all departments. Return department ID, average salary.

Sample table: employees

Sample Solution:

SELECT dep_id,
       avg(salary)
FROM employees
GROUP BY dep_id
HAVING avg(salary) <
  (SELECT avg(salary)
   FROM employees);

Sample Output:

 dep_id |          avg
--------+-----------------------
   3001 | 1633.3333333333333333
(1 row)

Explanation:

The said query in SQL that retrieves the department ID and average salary for each department where the average salary is less than the overall average salary for all employees in the table.

The GROUP BY clause groups the employees by department ID, and calculates the average salary for each group.

The HAVING clause filters only those groups where the average salary is less than the overall average salary for all employees in the 'employees' table.

The subquery in the HAVING clause calculates the overall average salary from the 'employees' table without any grouping.

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 get commissions, second highest salary.
Next SQL Exercise: Display the unique department of the employees.