Aggregation and grouping

Aggregate functions summarise many rows into one value: COUNT, SUM, AVG, MIN, MAX, and more such as STRING_AGG and BOOL_AND. With GROUP BY, they summarise each group instead of the whole table.

The rule of grouping

After GROUP BY, each output row is a group, so every column in SELECT must either be in the GROUP BY or be inside an aggregate. SELECT cause, reserve FROM claims GROUP BY cause fails: which of the claim's many reserves would it show?

Nulls in aggregates

• COUNT(*) counts rows; COUNT(column) counts the rows where the column is not null.
• SUM, AVG, MIN and MAX ignore nulls; the sum of no rows is null, not 0: wrap it in COALESCE(SUM(x), 0).

SELECT COUNT(*) AS policies, COUNT(broker_id) AS through_a_broker
FROM policies;

where or having

WHERE filters rows before grouping; HAVING filters groups after. 'Claims in 2025' is a WHERE; 'causes with more than 100 claims' is a HAVING, because the count exists only after grouping.

SELECT cause, COUNT(*) AS claims, SUM(reserve) AS reserves
FROM claims
GROUP BY cause
HAVING COUNT(*) > 1
ORDER BY cause;

Ratios: sum first, then divide

A portfolio's loss ratio is total claims divided by total premium, not the average of each policy's ratio: the average of ratios gives a small policy with one large claim the same weight as a large one.

Subqueries

A subquery is a query inside another. It can return a single value (in WHERE reserve > (SELECT AVG(reserve) ...)), a list (after IN), or a table (in FROM). A correlated subquery refers to the outer row, and conceptually runs once per row.

SELECT cause, COUNT(*) AS claims
FROM claims
GROUP BY cause
HAVING COUNT(*) > 100
ORDER BY claims DESC;