PostgreSQL / Basic: relational foundations / Lesson 5
Aggregates and reporting stages
Produce reliable counts, totals and conditional summaries. Separate pre-group filtering from group filtering and distinguish an empty set from a zero measure.
On this page
Lesson goals
- Explain COUNT, SUM, GROUP BY, HAVING and FILTER.
- Validate group grain, NULL behavior and deterministic output.
Grouping changes what a row means
Before grouping, a row may represent one payment. After GROUP BY customer_id, it represents one customer summary. Every projected value must follow a valid grouping or aggregate rule; do not rely on permissive behavior copied from another database. WHERE filters input rows before aggregation, while HAVING selects completed groups. A report of customers whose total exceeds fifty differs from a report that first removes every payment of fifty or less. Write that distinction in plain language before SQL.
Observe how NULL changes aggregate inputs
The fixture contains two known amounts for customer 1 and one NULL amount for customer 2. COUNT(*) counts rows, COUNT(amount) counts non-NULL amounts and SUM ignores NULL inputs but returns NULL when no non-NULL input exists. The output therefore distinguishes one unknown payment record from no payment rows. COALESCE can display zero when that is the report policy, but applying it prematurely can hide missing financial facts that should instead trigger investigation.
SELECT customer_id, COUNT(*) AS records,
COUNT(amount) AS known_amounts, SUM(amount) AS total
FROM (VALUES (1, 10::numeric), (1, 15::numeric),
(2, NULL::numeric)) AS p(customer_id, amount)
GROUP BY customer_id ORDER BY customer_id;customer_id | records | known_amounts | total
1 | 2 | 2 | 25
2 | 1 | 0 | NULLThe NULL total is a deliberate unknown result, not a fabricated zero.
FILTER documents conditional aggregates
PostgreSQL supports FILTER on aggregate calls. It lets one report count paid and pending records without duplicating source joins or moving the entire report population into one condition. COUNT(*) FILTER counts qualifying rows, while SUM(amount) FILTER can still be NULL when no qualifying known amounts exist. Conditions obey three-valued logic: unknown is not true. Decide whether cancelled bookings, refunds and unknown statuses belong to a metric before presenting it as a meaningful operational number.
Compute two metrics from one fixture
This self-contained relation has two paid rows and one pending row. The conditional counts are two and one, while the paid amount is thirty. A WHERE state = paid would eliminate pending input before its count could be calculated. FILTER leaves the shared population visible to each aggregate and changes only that aggregateโs input. The example uses a stable literal fixture; it makes no claim about real payment throughput or observed production totals.
SELECT COUNT(*) FILTER (WHERE state = 'paid') AS paid_count,
COUNT(*) FILTER (WHERE state = 'pending') AS pending_count,
SUM(amount) FILTER (WHERE state = 'paid') AS paid_total
FROM (VALUES ('paid', 10), ('paid', 20), ('pending', 5))
AS p(state, amount);paid_count | pending_count | paid_total
2 | 1 | 30All three metrics are derived from the same three input records.
Ordering and arithmetic require review
GROUP BY does not guarantee result order; add ORDER BY for exports and stable comparisons. Integer division truncates in PostgreSQL when both operands are integer types, so cast an operand to numeric for ratios. AVG ignores NULL values, which changes its denominator relative to COUNT(*). Reconcile a sample report by hand at its documented grain, including empty inputs and repeated amounts. A query that executes successfully can still answer the wrong question when the population or denominator is poorly defined.
Common errors and fixes
column must appear in GROUP BY (42803)
- Why it happens
- A projected value has no valid grouping or aggregate rule.
- How to fix it
- Group the intended dimensions or aggregate with a justified rule.
- Prevent it next time
- State the report grain before choosing output columns.
A ratio unexpectedly becomes zero
- Why it happens
- Both operands used integer division.
- How to fix it
- Cast one operand to numeric and define zero-denominator handling.
- Prevent it next time
- Test fractions and empty populations in report calculations.
An outer-join count shows one for an empty child set
- Why it happens
- COUNT(*) counted the preserved parent row.
- How to fix it
- Count the non-NULL child key when counting children.
- Prevent it next time
- Include a parent with no children in aggregate fixtures.
Check your understanding
1. The report has amounts 10, NULL and 20. Compare COUNT(*), COUNT(amount), SUM and AVG.
Show worked solution
COUNT(*) is 3, COUNT(amount) is 2, SUM is 30 and AVG is 15 over the two known amounts. Replacing NULL with zero before AVG would instead yield 10 and assert a different business meaning. Decide whether the unknown record is incomplete data, an intentional absence or a zero-value event before choosing a denominator.
2. A success ratio uses successes / attempts, both integers. Show a safe expression and explain a zero attempt count.
Show worked solution
Use successes::numeric / NULLIF(attempts, 0). The cast preserves a fractional ratio and NULLIF prevents division by zero. A zero denominator produces NULL, meaning the rate is undefined rather than a measured zero success rate. Presentation can label it unavailable; avoid reporting 0% as though failed attempts had actually occurred.
SELECT 1::numeric / NULLIF(4, 0) AS ratio;ratio
0.25Key takeaways
- NULL-aware denominators and explicit grain make totals interpretable.
- FILTER and HAVING operate at different reporting stages.