PostgreSQL Aggregates and Grouping Cheat Sheet

WITH orders (customer_id, shipped_at, amount) AS (
  VALUES (1, DATE '2026-01-01', 20), (1, NULL, 30), (2, DATE '2026-01-02', 50)
)
SELECT COUNT(*) AS rows,
       COUNT(shipped_at) AS shipped,
       COUNT(DISTINCT customer_id) AS customers,
       SUM(amount) AS total,
       AVG(amount) AS average
FROM orders;

rows | shipped | customers | total | average
-----+---------+-----------+-------+---------
3    | 2       | 2         | 100   | 33.3333

WITH orders (region, status, amount) AS (
  VALUES ('East', 'paid', 40), ('East', 'paid', 20),
         ('East', 'open', 15), ('West', 'paid', 30)
)
SELECT region, status, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
GROUP BY region, status
ORDER BY region, status;

region | status | orders | total
-------+--------+--------+------
East   | open   | 1      | 15
East   | paid   | 2      | 60
West   | paid   | 1      | 30

WITH results (season, team, score) AS (
  VALUES (2026, 'A', 60), (2026, 'A', 50), (2026, 'B', 70), (2025, 'B', 80)
)
SELECT team, SUM(score) AS total
FROM results
WHERE season = 2026
GROUP BY team
HAVING SUM(score) >= 100
ORDER BY team;

team | total
-----+------
A    | 110

WITH orders (region, status, amount) AS (
  VALUES ('East', 'paid', 40), ('East', 'open', 15), ('East', 'paid', 20)
)
SELECT region,
       COUNT(*) AS all_orders,
       COUNT(*) FILTER (WHERE status = 'paid') AS paid_orders,
       SUM(amount) FILTER (WHERE status = 'paid') AS paid_total,
       COUNT(*) FILTER (WHERE status = 'open') AS open_orders
FROM orders
GROUP BY region;

region | all_orders | paid_orders | paid_total | open_orders
-------+------------+-------------+------------+------------
East   | 3          | 2           | 60         | 1

WITH members (team, id, name, joined_at, approved) AS (
  VALUES ('Docs', 2, 'Grace', DATE '2026-02-01', true),
         ('Docs', 1, 'Ada', DATE '2026-01-01', true)
)
SELECT team,
       STRING_AGG(name, ', ' ORDER BY name) AS names,
       ARRAY_AGG(id ORDER BY joined_at, id) AS ids,
       BOOL_AND(approved) AS all_approved
FROM members
GROUP BY team;

team | names      | ids   | all_approved
-----+------------+-------+-------------
Docs | Ada, Grace | {1,2} | true

WITH sales (region, product, amount) AS (
  VALUES ('East', 'A', 10), ('East', 'B', 20), ('West', 'A', 15)
)
SELECT region, product, SUM(amount) AS total,
       GROUPING(region, product) AS grouping_mask
FROM sales
GROUP BY ROLLUP (region, product)
ORDER BY GROUPING(region, product), region, product;

region | product | total | grouping_mask
-------+---------+-------+--------------
East   | A       | 10    | 0
East   | B       | 20    | 0
West   | A       | 15    | 0
East   | NULL    | 30    | 1
West   | NULL    | 15    | 1
NULL   | NULL    | 45    | 3

WITH teams (id) AS (VALUES (1), (2)),
scores (team_id, points) AS (VALUES (1, 10), (1, 20))
SELECT t.id,
       COUNT(s.team_id) AS score_rows,
       COALESCE(SUM(s.points), 0) AS points
FROM teams AS t
LEFT JOIN scores AS s ON s.team_id = t.id
GROUP BY t.id
ORDER BY t.id;

id | score_rows | points
---+------------+-------
1  | 2          | 30
2  | 0          | 0