AdvancedGROUP BY & Aggregation

Pivot-style aggregation using CASE

The query

SQL

SELECT
  dept_id,
  COUNT(*) AS total,
  SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active,
  SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive,
  SUM(CASE WHEN status = 'on_leave' THEN 1 ELSE 0 END) AS on_leave
FROM employees
GROUP BY dept_id
ORDER BY dept_id;

Tested against PostgreSQL 16

Note

“Manual pivot using CASE inside SUM. Useful when crosstab() or tablefunc extension isn't available.

Tables referenced

employees10 cols