IntermediateBasic SELECT

Show department-wise headcount and average salary

The query

SQL

SELECT
  dept_id,
  COUNT(*) AS headcount,
  ROUND(AVG(salary), 0) AS avg_salary,
  SUM(salary) AS total_salary_cost
FROM employees
WHERE status = 'active'
GROUP BY dept_id
ORDER BY total_salary_cost DESC;

Tested against PostgreSQL 16

Note

“Multiple aggregates on grouped data in a single query.

Tables referenced

employees10 cols