186: Aggregation: COUNT, SUM, AVG, MIN/MAX, GROUP BY, HAVING, and Conditional Aggregates

Learning outcomes

By the end of this lesson, you can:

explain and apply aggregate functions in a realistic implementation;
explain and apply group by in a realistic implementation;
explain and apply having in a realistic implementation;
explain and apply conditional aggregation in a realistic implementation;
explain and apply distinct aggregates in a realistic implementation.

Prerequisites and retrieval

This lesson assumes the earlier 01–06 foundation and the preceding lessons in this module. Before you read, retrieve one concrete example from a previous project where you had to summarize records, count related records, or decide which rows belonged in a report. The point is not to memorize a list of SQL keywords. It is to make a defensible decision in a PostgreSQL-backed transactional application, where schema design, correctness, query plans, and concurrency all affect the result.

Terminology

Aggregate functions: COUNT, SUM, AVG, MIN, and MAX summarize a set of rows into values such as a count, total, average, or boundary value. Their treatment of NULL and their numeric return types matter.
GROUP BY: Grouping forms one result row for each distinct grouping-key set. It changes the granularity of the result, so selected values must make sense at that level.
HAVING: WHERE filters input rows before grouping; HAVING filters groups after aggregation. Confusing those stages can change both the meaning and the cost of a query.
Conditional aggregation: Use PostgreSQL's FILTER (WHERE ...) clause, or a CASE expression where appropriate, to calculate a metric only for rows meeting a condition while retaining the same group. Treat the condition as part of the metric's definition, not merely as vocabulary.
Distinct aggregates: COUNT(DISTINCT x) answers a different question from COUNT(x): it counts unique non-NULL values rather than every non-NULL row.
Grouping sets: Advanced reporting can use ROLLUP, CUBE, and GROUPING SETS to produce multiple aggregation levels in one query.

Mental model

Treat Aggregation: COUNT, SUM, AVG, MIN/MAX, GROUP BY, HAVING, and Conditional Aggregates as a design problem with observable inputs, outputs, invariants, and failure modes. An aggregate does not just calculate a number; it changes what one output row represents. Once rows are grouped, every selected value must be valid at that grouping level, and every denominator must describe the population you actually intend to measure.

For example, a “paid-order rate” could mean paid orders divided by all orders, paid orders divided by orders that have been invoiced, or the percentage of customers with at least one paid order. Those are different contracts. Write the contract down before choosing the expression. A strong implementation makes assumptions visible, narrows uncertainty at boundaries, and leaves enough evidence—tests, types, constraints, metrics, or diagrams—to show why the design is safe.

A useful interview and production sequence is:

requirement -> constraints -> model -> implementation -> failure analysis -> verification

Do not jump from a requirement directly to a library call or a familiar query pattern. First state what must remain true. Then choose the SQL mechanism that enforces that invariant, and verify the result against cases that could expose a wrong denominator, a NULL, or a duplicate join row.

Deep dive

1. Aggregate functions

The first problem is usually that the application has many rows but needs one useful summary: how many orders exist, how much was charged, or what the smallest and largest values are. COUNT, SUM, AVG, MIN, and MAX are aggregate functions for this purpose. They summarize rows rather than returning one row per input record.

There is a subtle detail worth knowing: NULL is not the same as zero, and most aggregates ignore NULL values. COUNT(*) counts input rows, while COUNT(column) counts rows where that column is not NULL. SUM and AVG also depend on the non-NULL values they receive, and the numeric return type can affect precision. Check the database documentation and the schema types when totals or averages cross an application boundary.

Decision rule: Use aggregate functions deliberately when they make the contract or invariant easier to prove. If an aggregate only reduces typing while hiding an assumption about NULL, duplicates, precision, or the population being measured, make that assumption explicit instead.

2. GROUP BY

The next problem is that a single total is not enough. A report may need one result per branch, customer, or day. GROUP BY forms one result row per distinct grouping key set. In practical terms, it changes the question from “what is the total?” to “what is the total for each value of these keys?”

Selected non-aggregate columns must be functionally determined according to the database's grouping rules. If the query groups by branch_id, selecting an unrelated branch name without a valid relationship to that key is not a safe shortcut. Include the required key columns or aggregate a value whose meaning at that level is clear. This is where people usually get confused: a column being present in the underlying table does not mean it is valid in the grouped result.

Decision rule: Use GROUP BY deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding the result row's granularity or a functional-dependency assumption, prefer the more explicit design.

3. HAVING

The useful distinction is timing. WHERE filters input rows before the database forms groups; HAVING filters the groups after aggregation. Use WHERE for row-level facts, such as excluding inactive customers before counting orders. Use HAVING for group-level facts, such as retaining only customers whose order count is greater than ten.

When a predicate can be applied in WHERE without changing semantics, push it there. That can reduce the rows that must be joined and grouped. Do not move a predicate merely for performance if doing so changes the population represented by the metric; verify the meaning first and inspect the execution plan when performance matters.

Decision rule: Use HAVING deliberately when it makes the group-level contract or invariant easier to prove. If the condition is actually row-level, putting it in HAVING can hide its meaning and make the database do unnecessary work.

4. Conditional aggregation

Reports often need several related metrics from the same grouped rows: total invoices, paid invoices, refunded invoices, and perhaps a rate. PostgreSQL's FILTER (WHERE ...) lets each aggregate declare which rows it counts or sums. A CASE expression can express the same kind of conditional calculation where FILTER is unavailable or where the expression needs a value transformation.

The advantage is not simply shorter SQL. The conditions sit next to the metrics they define, so the query makes the report contract easier to inspect. You can calculate multiple measures from the same grouped input without repeating joins or scans in the application. Still, inspect the joins: conditional aggregation does not automatically protect a total from duplicate joined rows.

Decision rule: Use conditional aggregation deliberately when it makes the contract or invariant easier to prove. If the condition is unclear, the denominator is wrong, or a join multiplies rows, rewrite the input relation or pre-aggregate before trusting the result.

5. Distinct aggregates

COUNT(DISTINCT x) answers a different question from COUNT(x). The former counts unique non-NULL values; the latter counts every row whose x is non-NULL. If one customer has three invoices, counting invoice rows and counting distinct customers should produce different values. Neither is universally correct; the requirement determines which population matters.

Multiple distinct aggregates can be expensive. Depending on the data and plan, the database may need substantial sorting or hashing, and a large or high-cardinality input can make that visible in memory and execution time. Pre-aggregation or an alternate query plan may be better. Use EXPLAIN or EXPLAIN ANALYZE with care, and measure representative data rather than assuming that a query is inexpensive because it is only one statement.

Decision rule: Use distinct aggregates deliberately when they make the contract or invariant easier to prove. If they conceal a duplicated join or create unacceptable resource usage, fix the input shape or choose a measured alternative.

6. Grouping sets

Advanced reports sometimes need detail by branch, subtotals by region, and a grand total. ROLLUP, CUBE, and GROUPING SETS can produce multiple aggregation levels in one query. The resulting NULL grouping columns may represent a subtotal rather than missing source data, so the output contract must distinguish those cases when the client displays or stores the result.

These features are useful when they genuinely simplify reporting, not merely because they can replace several familiar queries. Verify the plan and the result labels, especially when the report is large or consumed by code that cannot tell a subtotal from an actual NULL key.

Decision rule: Use grouping sets deliberately when they make the reporting contract or invariant easier to prove. If they make the output ambiguous or the plan difficult to operate, use a clearer alternative and document the trade-off.

Worked example

Consider a PostgreSQL-backed transactional application where schema design, correctness, query plans, and concurrency all matter. Suppose the requirement is to list active customers, including customers with no orders, with their order counts. Start by writing that requirement in one sentence, list the input and output contracts, and identify which concept owns each possible failure mode.

The important move is separation. Parsing or validation belongs at the boundary; domain rules belong in the domain or service layer; persistence rules belong in the database or repository; presentation rules belong in the client. Mixing those concerns can make a happy-path demo look shorter, but it makes edge cases much harder to reason about. In this query, the LEFT JOIN preserves an active customer even when no matching order exists, and COUNT(o.id) counts only matching order IDs rather than the preserved customer row.

SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.status = 'active'
GROUP BY c.id, c.name
ORDER BY order_count DESC;

Walk through at least four cases: the normal path; an empty or missing value; a duplicate, retry, or concurrent path where relevant; and a dependency failure. For the normal path, confirm that each customer appears once and that the count matches the intended order rows. For a customer with no orders, confirm that the LEFT JOIN preserves the customer and that COUNT(o.id) returns zero. For duplicates, ask whether they are genuine orders or duplicate join rows; COUNT(DISTINCT ...) may answer a different question, but it is not a repair for an incorrect relationship. For a dependency failure, state which layer detects the database or transaction error and what the caller observes.

This is the level of explanation expected in a senior code review or technical interview: do not stop at syntax. Explain the row shape before grouping, the row shape after grouping, the effect of NULL, and the invariant the query is meant to preserve.

Production perspective

Production correctness is broader than “the code works on my machine.” Ask how the design behaves during deploys, retries, partial failure, stale clients, concurrent requests, malformed data, schema changes, and high cardinality. Aggregates over a changing transactional database need a defined consistency expectation: the result may be a statement-level view, a transaction-level view, or a deliberately approximate report. The query cannot choose that business meaning for you.

Prefer explicit contracts, bounded resource usage, structured errors, and measurable behavior. Optimize only after you can identify the bottleneck or risk with evidence. Check indexes and join cardinality, inspect execution plans, and test data volumes that resemble the real workload. A correct aggregate can still be operationally unsafe if it spills heavily, holds a transaction open too long, or returns an unexpectedly large result.

When the topic involves an external dependency, define a timeout and cancellation strategy. When it involves persistence, define transaction and consistency expectations. When it involves user-visible state, define loading, empty, error, stale, and success states. When it involves security, assume the client can be modified and the network input is untrusted. Parameterize values, enforce authorization outside the client's control, and do not let a report query expose rows the caller is not allowed to see.

Guided lab

Produce daily sales metrics per branch: invoice count, distinct customers, gross amount, refunded amount, and paid-order rate. Verify that NULL values and duplicate join rows do not distort totals. Before writing SQL, define what a day means, which timestamp and time zone determine the day, which invoice statuses count as paid or refunded, and the exact denominator for the paid-order rate.

Complete the lab with this discipline:

Write the requirement and two non-requirements.
List input, output, and error contracts before implementation.
Implement the smallest correct vertical slice.
Add at least one invalid-input test and one edge-case test.
Instrument or inspect the behavior instead of guessing.
Refactor one hidden assumption into an explicit type, constraint, function, or configuration.
Explain one alternative design and why you did not choose it.
Record a short “what would break at 10× scale?” note.

Your verification should include a branch with no invoices, invoices with NULL values where the schema permits them, a customer with multiple invoices, and a join path that could multiply an invoice row. Compare the result with a small hand-calculated fixture. Then inspect the query plan and record which part would need attention at ten times the data volume.

Edge cases and failure modes

Aggregate functions: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Check the difference between COUNT(*), COUNT(column), and the behavior of SUM or AVG when values are NULL.
GROUP BY: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Confirm that every output column has a valid meaning at the group level.
HAVING: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Verify that moving a predicate between WHERE and HAVING does not silently change the population.
Conditional aggregation: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Test each condition and the denominator independently.
Distinct aggregates: test absence, malformed input, duplicates, ordering/concurrency where applicable, and behavior at the smallest and largest credible sizes. Include repeated values and NULL, and measure high-cardinality cases.

Common mistakes and debugging

Solving the example instead of the requirement: a copied pattern can be syntactically correct but architecturally wrong. A left join, a distinct count, or a filtered aggregate is correct only for the population it is intended to represent.
Hiding uncertainty with assertions, broad exception handlers, permissive schemas, or “temporary” any values. These can make a wrong result look acceptable instead of forcing the contract to be explicit.
Testing only the happy path and therefore discovering contracts only after integration. Include no rows, NULL, duplicates, and concurrent or retry-related states where they affect the metric.
Optimizing before measuring, or selecting a scalable mechanism without a scale requirement. Inspect the actual query plan and representative data first.
Letting client-side behavior stand in for server-side authorization, validation, or persistence guarantees. The client is not a security boundary.

For debugging, reproduce the smallest failing case, inspect the actual values or execution plan, trace the boundary where the invariant first becomes false, and fix the owning layer rather than adding a downstream patch. In SQL, start by checking the ungrouped joined rows. If a count is too high, look for row multiplication before reaching for DISTINCT; if a group is missing, check the join type and WHERE predicates; if a total is unexpectedly NULL, check the aggregate input and the intended empty-set behavior.

Interview questions

What problem do aggregate functions solve, and what trade-off or failure mode would make you choose a different approach?
What problem does GROUP BY solve, and what trade-off or failure mode would make you choose a different approach?
What problem does HAVING solve, and what trade-off or failure mode would make you choose a different approach?
What problem does conditional aggregation solve, and what trade-off or failure mode would make you choose a different approach?
What problem do distinct aggregates solve, and what trade-off or failure mode would make you choose a different approach?

Checkpoint

Without notes, explain Aggregation: COUNT, SUM, AVG, MIN/MAX, GROUP BY, HAVING, and Conditional Aggregates to another developer in five minutes. Your explanation must include one invariant, one edge case, one production failure mode, and one alternative design. Then implement a small example without copying the lesson code. As part of the explanation, make clear what one output row represents and why the chosen denominator and NULL behavior are correct.

Mastery checklist

I can define the core terms precisely.
I can choose a design from requirements instead of from habit.
I can implement and test the normal path and edge cases.
I can explain the runtime, storage, or complexity cost.
I can identify which layer owns validation, errors, and recovery.
I can compare at least two reasonable alternatives.
I can explain how the design changes at larger scale or stricter reliability.