IntermediateGROUP BY & Aggregation

GROUPING SETS: custom aggregation combinations

The query

SQL

SELECT
  dept_id,
  job_title,
  COUNT(*) AS count,
  ROUND(AVG(salary), 0) AS avg_salary
FROM employees
GROUP BY GROUPING SETS(
  (dept_id, job_title),
  (dept_id),
  (job_title),
  ()
)
ORDER BY dept_id NULLS LAST, job_title NULLS LAST;

Tested against PostgreSQL 16

Note

“GROUPING SETS = explicit union of multiple GROUP BY. More flexible than ROLLUP/CUBE.

Tables referenced

employees10 cols