AdvancedGROUP BY & Aggregation

FILTER clause in aggregation

The query

SQL

SELECT
  dept_id,
  COUNT(*) AS total_employees,
  COUNT(*) FILTER (WHERE salary > 70000) AS high_earners,
  COUNT(*) FILTER (WHERE status = 'active') AS active_count,
  SUM(salary) FILTER (WHERE status = 'active') AS active_salary_cost
FROM employees
GROUP BY dept_id
ORDER BY dept_id;

Tested against PostgreSQL 16

Note

“FILTER clause on aggregates (PostgreSQL 9.4+) is cleaner than using CASE WHEN inside aggregate.

Tables referenced

employees10 cols