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.

EasyJOINs (INNER, LEFT, RIGHT, FULL)

Get order total per customer with name

The query

SQL
SELECT
  c.name AS customer,
  COUNT(o.order_id) AS order_count,
  SUM(o.total) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id
GROUP BY c.cust_id, c.name
ORDER BY total_spent DESC NULLS LAST;
Tested against PostgreSQL 16

Note

“LEFT JOIN + GROUP BY pattern to aggregate per-customer orders including customers with zero orders.

Tables referenced