PostgreSQL Aggregate Functions and Group By: Get the average salary for all departments working more than 10 employees

14. Write a query to get the average salary for all departments working more than 10 employees.

Sample Solution:

Code:

-- This SQL query calculates the average salary and counts the number of employees for each department, 
-- filtering out departments with fewer than 10 employees.

SELECT department_id, -- Selects the department_id column
AVG(salary), -- Calculates the average salary for each department
COUNT(*) -- Counts the number of employees in each department
FROM employees -- Specifies the table from which to retrieve data, in this case, the employees table
GROUP BY department_id -- Groups the results by department_id, so that the calculations are done for each unique department_id
HAVING COUNT(*) > 10; -- Filters the grouped results to include only those departments where the count of employees is greater than 10

Explanation:

• This SQL query calculates the average salary and counts the number of employees for each department.
• The SELECT statement selects the department_id, the average salary, and the count of employees for each department.
• The AVG(salary) function calculates the average salary for each department.
• The COUNT(*) function counts the number of employees in each department.
• The FROM clause specifies the table from which to retrieve the data, which is the employees table.
• The GROUP BY clause groups the results by department_id, ensuring that the calculations are done for each unique department.
• The HAVING clause filters the grouped results to include only those departments where the count of employees is greater than 10.
• The result set will contain one row for each department where the count of employees is greater than 10, along with the average salary and the count of employees for each such department.

Sample table: employees

Output:

pg_exercises=# SELECT department_id, AVG(salary), COUNT(*)
pg_exercises-# FROM employees
pg_exercises-# GROUP BY department_id
pg_exercises-# HAVING COUNT(*) > 10;
 department_id |          avg          | count
---------------+-----------------------+-------
            80 | 8955.8823529411764706 |    34
            50 | 3475.5555555555555556 |    45
(2 rows)

Relational Algebra Expression:

Relational Algebra Tree:

Practice Online

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

Previous: Write a query to get the job ID and maximum salary of each post for maximum salary is at or above $4000.
Next: PostgreSQL String() Function - Exercises, Practice, Solution