Summarise at a declared grain

Estimated study time: 60–90 minutes. Read, predict, run, explain, then attempt the independent work.

A row-level query can show every order, but a manager often needs one row per channel or month. GROUP BY changes the output grain. It collects rows with the same grouping values so aggregate functions can reduce each collection to a measure. COUNT counts, SUM adds, AVG averages, and MIN and MAX choose the smallest or largest non-null value. An aggregate without GROUP BY summarises the whole selected input.

The first example starts with completed orders, groups them by channel, and counts the rows in each group. Because orders already has one row per order, COUNT(*) means completed orders here. The same expression over joined order lines would mean lines instead. There is no universal “count orders” function that repairs an incorrect input grain for you.

A grouped result should select its grouping keys and aggregate expressions. Asking for an unrelated detail such as order_id in a one-row-per-channel report is ambiguous: which order would represent the channel? PostgreSQL rejects many such combinations. Rather than treating the error as a nuisance, use it to reconsider what one output row is supposed to mean.

WHERE and HAVING answer different questions

WHERE filters source rows before the groups are made. HAVING filters complete groups after their aggregates exist. To ask for channels with at least five completed orders, first remove noncompleted rows with WHERE, then use HAVING COUNT(*) >= 5. Putting the status condition only in a later layer could mix cancelled or pending orders into the count.

An alias created in SELECT is not generally available to WHERE or HAVING at the same query level. Write the aggregate in HAVING or use a later query layer, introduced in module 8. The conceptual flow diagram is a reasoning aid, not a promise about the physical execution plan. A database optimizer may implement an equivalent computation in a different order.

Open the diagram at full size

Read the diagram explanation

Read FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, and ORDER BY with LIMIT. A database optimizer may execute an equivalent physical plan.

Count completeness honestly

COUNT(*) counts input rows, including rows that contain a null in some columns. COUNT(city) counts only rows with a known city. Their difference is the number with a missing city. COUNT(DISTINCT city) counts different non-null city values. These are three different questions. Use output names that reveal which one was answered.

Most ordinary aggregates ignore null input values. On an empty input, COUNT returns zero while SUM returns NULL. There are no refunds in 2026 in our fixture; that absence produces a null sum rather than a numerical observation. For a display of “total recorded refunds in the period,” COALESCE can intentionally label the absence as zero. Keep the raw interpretation clear in your report. A missing refund feed is not equivalent to a verified period with no refunds.

Averages also require a grain and weighting decision. An average of line amounts answers “typical line,” not “typical order.” An unweighted average of monthly average order values gives each month equal influence, even if the months contain different order counts. For the quarter's average order value, sum the quarter's sales and divide by the quarter's completed orders. Module 12 will make this a verification exercise.

When validating an aggregate, check the groups, totals and exclusions. App plus web completed-order counts should equal the completed-order count before grouping. Reconciliation will not detect every error, especially if both queries share the same wrong filter, so combine it with boundary and hand-worked cases.

Worked example 05 group

App has five completed orders and web has six. HAVING retains both groups. Raising the threshold to six should leave only web.

SELECT channel, COUNT(*) AS completed_orders
FROM aabha_sql.orders WHERE status='completed'
GROUP BY channel HAVING COUNT(*) >= 5 ORDER BY channel;

Expected result from the bundled dataset:

The runnable file is sql/examples/05_group.sql.

Worked example 05 missing

The result separates table coverage from city completeness. Eight customer rows do not imply eight known cities.

SELECT COUNT(*) AS customers, COUNT(city) AS known_cities,
 COUNT(*)-COUNT(city) AS missing_cities FROM aabha_sql.customers;

Expected result from the bundled dataset:

The runnable file is sql/examples/05_missing.sql.

Worked example 05 empty

The empty aggregate still returns one summary row. Compare COUNT, the raw SUM and a deliberately labelled display value.

SELECT COUNT(*) AS row_count, SUM(amount) AS raw_sum,
 COALESCE(SUM(amount),0) AS display_sum
FROM aabha_sql.refunds WHERE refund_date >= DATE '2026-01-01';

Expected result from the bundled dataset:

The runnable file is sql/examples/05_empty.sql.

Independent practice

05a

Return each order status and its count as orders. Include every status and sort alphabetically.

Hint: Do not apply the completed-only filter to this question.

05b

Return customer_id and completed_orders for customers with at least two completed orders, sorted by customer_id.

Hint: Filter rows with WHERE, then filter customer groups with HAVING.

Check before moving on

State the grain before and after GROUP BY. Explain why a quarter-wide average order value is not the simple average of three monthly averages.