Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

IntermediateBasic SELECT

Conditional count: active vs inactive employees

The query

SQL
SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'active') AS active,
  COUNT(*) FILTER (WHERE status = 'inactive') AS inactive,
  COUNT(*) FILTER (WHERE status = 'on_leave') AS on_leave,
  ROUND(COUNT(*) FILTER (WHERE status = 'active') * 100.0 / COUNT(*), 1) AS active_pct
FROM employees;
Tested against PostgreSQL 16

Note

“FILTER clause is cleaner than CASE WHEN inside COUNT. PostgreSQL 9.4+ feature.

Tables referenced