05 — Aggregate Queries: Collapsing Rows Into Summaries

Reaching for aggregates one at a time — COUNT here, SUM there — was my approach until the pattern underneath appeared. Writing them down made one idea click: an aggregate function collapses many rows into a single value, and GROUP BY is what decides the granularity of that collapse — one summary for the whole table, or one summary per bucket. [1][2]

The framing that landed is that aggregation is a shape change. A query that returns one row per order can be collapsed into one row per customer, or one row per month, or one row for the entire table. The aggregate function (SUM, COUNT, …) picks the calculation; GROUP BY picks the bucket size. Get those two right and the report writes itself.

The five core aggregates

The standard aggregate functions all share the same shape: many input rows, one output value [1].

• *`COUNT()** — number of rows in the group. COUNT(column)` counts non-null values in that column [3].
• SUM(col) — total of a numeric column. Ignores nulls [2].
• AVG(col) — average of a numeric column, i.e. SUM(col) / COUNT(non-null col) [4][5].
• MIN(col) — smallest value, works on numbers, dates, and strings [6].
• MAX(col) — largest value, same type support as MIN [7].

One detail worth fixing in memory: aggregates ignore nulls. AVG(spend) over rows [10, 20, NULL] is (10+20)/2 = 15, not 30/3. COUNT(spend) on the same set is 2. When I need the "treat null as zero" behavior, I reach for COALESCE(spend, 0) before aggregating.

GROUP BY — choose the bucket

Without GROUP BY, an aggregate collapses the whole table to one row:

SELECT SUM(total) AS grand_total FROM orders;   -- one row

Add GROUP BY and the collapse happens per distinct value of the grouping column [8][9]:

SELECT customer_id, SUM(total) AS spent
FROM orders
GROUP BY customer_id;                            -- one row per customer

The way of thinking: GROUP BY sorts rows into buckets by the named column, runs the aggregates inside each bucket, and emits one summary row per bucket. Group by customer_id → one row per customer; group by DATE(order_placed_at) → one row per day.

HAVING — filter the summary

HAVING filters the buckets after aggregation [10][11]. It's the only place I can put a condition on an aggregate, because WHERE runs before grouping:

SELECT customer_id, SUM(total) AS spent
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 1000;

The rule I keep: row-level condition → WHERE; group-level condition → HAVING. Putting a WHERE-able condition in HAVING wastes work, because the engine aggregates rows it was about to throw away.

Scalar: the single-value subquery

The roadmap also flags scalar values, and the tie-in matters [12][13]. A scalar subquery returns exactly one column and one row — a single value I can drop into a SELECT, a WHERE, or an expression:

SELECT name, total,
       (SELECT AVG(total) FROM orders) AS overall_avg
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

A scalar subquery is how I compare each row against a single computed benchmark without a JOIN. Aggregates produce scalar values when used without GROUP BY, which is why (SELECT AVG(total) FROM orders) slots cleanly into the WHERE.

How I use this

The habit I keep is naming the bucket before naming the calculation. Before I write any aggregate query I ask: what is one row of the output? One row per customer, per day, per product category — that answer is the GROUP BY. Then I pick the aggregate (SUM for totals, COUNT for volumes, AVG for rates) and finally the filter (WHERE for raw rows, HAVING for the summary). Getting the bucket right first is what stops the "the numbers look wrong" debugging spiral, which almost always traces back to grouping at the wrong granularity.

References

[1] Programiz, "SQL GROUP BY," programiz.com, 2024. [Online]. Available: https://www.programiz.com/sql/group-by

[2] StudySmarter, "SQL SUM," studysmarter.co.uk, 2024. [Online]. Available: https://www.studysmarter.co.uk/explanations/computer-science/databases/sql-sum/

[3] DataCamp, "COUNT SQL Function," datacamp.com, 2024. [Online]. Available: https://www.datacamp.com/tutorial/count-sql-function

[4] SQLShack, "SQL AVG function introduction and examples," sqlshack.com, 2024. [Online]. Available: https://www.sqlshack.com/sql-avg-function-introduction-and-examples/

[5] W3Schools, "SQL AVG() Function," w3schools.com, 2024. [Online]. Available: https://www.w3schools.com/sql/sql_avg.asp

[6] Programiz, "SQL MAX & MIN," programiz.com, 2024. [Online]. Available: https://www.programiz.com/sql/min-and-max

[7] TechOnTheNet, "MAX," techonthenet.com, 2024. [Online]. Available: https://www.techonthenet.com/sql/max.php

[8] YouTube, "COUNT, SUM, AVG, MIN, MAX — Aggregating Data," 2023. [Online]. Available: https://www.youtube.com/watch?v=muwEdPsx534

[9] YouTube, "Advanced Aggregate Functions in SQL," 2023. [Online]. Available: https://www.youtube.com/watch?v=nNrgRVIzeHg

[10] Programiz, "SQL HAVING Clause," programiz.com, 2024. [Online]. Available: https://www.programiz.com/sql/having

[11] YouTube, "HAVING Clause," 2023. [Online]. Available: https://www.youtube.com/watch?v=tYBOMw7Ob8E

[12] IBM, "Creating SQL Scalar Functions," ibm.com, 2024. [Online]. Available: https://www.ibm.com/docs/en/db2/11.5?topic=functions-creating-sql-scalar

[13] YouTube, "Using Scalar SQL to boost performance," 2023. [Online]. Available: https://www.youtube.com/watch?v=v8X5FGzzc9A