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.

EasyGROUP BY & Aggregation

Count employees by department

The query

SQL
SELECT
  d.dept_name,
  COUNT(e.emp_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name
ORDER BY employee_count DESC;
Tested against PostgreSQL 16

Note

“Include non-aggregated columns in GROUP BY. Using LEFT JOIN to include empty departments.

Tables referenced