Grouping and Aggregating Data
1. Using GROUP BY Clause
SELECT status, count(*) FROM orders GROUP BY status;
| Rule | Detail |
|---|
| SELECT list | Only grouped cols or aggregates |
| PK shortcut | Group by PK ⇒ all other columns of that table allowed |
2. Grouping by Multiple Columns
SELECT customer_id, status, count(*) FROM orders GROUP BY customer_id, status;
3. Using COUNT Function
SELECT count(*) AS total_rows,
count(email) AS rows_with_email,
count(DISTINCT country) AS distinct_countries
FROM customers;
| Form | Counts |
|---|
| count(*) | All rows incl. NULL |
| count(col) | Non-NULL col only |
| count(DISTINCT col) | Unique non-NULL |
4. Using SUM Function
SELECT customer_id, sum(total_cents) FROM orders GROUP BY customer_id;
5. Using AVG Function
SELECT date_trunc('day', placed_at) AS day,
avg(total_cents)::int AS avg_cents
FROM orders GROUP BY day;
6. Using MIN and MAX Functions
| Function | Applies To |
|---|
| min(x) | Numbers, strings, dates, any orderable type |
| max(x) | Same |
SELECT min(placed_at), max(placed_at) FROM orders;
7. Using Aggregate Functions with DISTINCT
SELECT count(DISTINCT customer_id) FROM orders;
SELECT array_agg(DISTINCT country ORDER BY country) FROM customers;
8. Filtering Groups
SELECT customer_id, count(*) FROM orders
GROUP BY customer_id HAVING count(*) > 10;
| Clause | Stage |
|---|
| WHERE | Before grouping (per row) |
| HAVING | After grouping (per group) |
9. Using FILTER Clause
SELECT
count(*) AS total,
count(*) FILTER (WHERE status = 'paid') AS paid,
sum(total_cents) FILTER (WHERE status='paid') AS paid_revenue
FROM orders;
Note: FILTER is cleaner than SUM(CASE WHEN ... THEN ... END).
10. Using STRING_AGG
SELECT customer_id,
string_agg(status, ', ' ORDER BY placed_at) AS history
FROM orders GROUP BY customer_id;
11. Using ARRAY_AGG
SELECT customer_id, array_agg(id ORDER BY placed_at DESC) AS recent_orders
FROM orders GROUP BY customer_id;
12. Using JSONB_AGG
SELECT customer_id,
jsonb_agg(jsonb_build_object('id', id, 'total', total_cents)
ORDER BY placed_at DESC) AS orders
FROM orders GROUP BY customer_id;
| Function | Output |
|---|
| jsonb_agg | JSON array |
| jsonb_object_agg(k,v) | JSON object |
13. Using BOOL_AND and BOOL_OR
SELECT customer_id,
bool_and(status = 'paid') AS all_paid,
bool_or(status = 'cancelled') AS any_cancelled
FROM orders GROUP BY customer_id;