SQL: Aggregation, GROUP BY & CASE

Chapter roadmap

Summarize many rows into useful measures using aggregate functions, groups, group filters, conditional logic, and careful treatment of NULL. Work through the concepts in order, type every example yourself, and keep a short debugging log that records predictions, errors, fixes, and what each fix taught you.

Learning objectives

Use aggregate functions
Group at the correct grain
Filter groups with HAVING
Build conditional measures with CASE

03.1

Aggregate functions

COUNT, SUM, AVG, MIN, and MAX calculate one result from a set of rows. COUNT(*) counts rows, while COUNT(column) ignores NULL in that column. AVG also ignores NULL, which matters when missing values should instead count as zero or trigger a data-quality warning.

Before running the example, trace each expression and write down the expected state or result. Then run it, compare the actual result, and change one input or rule to confirm that you understand which part controls the behavior.

Example

SELECT
    COUNT(*) AS order_count,
    SUM(total) AS revenue,
    AVG(total) AS average_order,
    MAX(total) AS largest_order
FROM orders;

Result

order_count | revenue | average_order | largest_order
125         | 9320.50 | 74.56         | 410.00

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

03.2

GROUP BY and query grain

GROUP BY creates one result row per unique group. Every selected item must either be grouped or aggregated. The grain describes what one output row represents; stating it first prevents accidental double counting.

Example

SELECT category, COUNT(*) AS product_count, AVG(price) AS average_price
FROM products
GROUP BY category
ORDER BY product_count DESC;

Result

category | product_count | average_price
Books    | 42            | 18.74
Games    | 19            | 31.20

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

03.3

WHERE versus HAVING

WHERE filters individual rows before grouping. HAVING filters completed groups after aggregates are calculated. Use WHERE whenever a condition does not depend on an aggregate because reducing rows earlier is clearer and often more efficient.

Example

SELECT customer_id, SUM(total) AS lifetime_value
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(total) >= 500;

Result

customer_id | lifetime_value
18          | 742.15
44          | 1250.00

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

03.4

CASE and conditional aggregation

CASE creates categories or conditional values inside a query. Summing a CASE expression counts or totals rows that meet a rule. Include an ELSE branch so unexpected values produce an intentional result instead of silent NULL.

Example

SELECT
    department,
    COUNT(*) AS employees,
    SUM(CASE WHEN remote = TRUE THEN 1 ELSE 0 END) AS remote_count
FROM employees
GROUP BY department;

Result

department | employees | remote_count
Design     | 12        | 8
Operations | 20        | 5

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

Chapter assignments

Complete these in order. Later assignments assume that earlier skills are working. Do not only test the sample values; design tests that challenge boundaries, missing values, incorrect types, empty collections, and other likely failures.

A3.1
Create a one-row dashboard of counts, totals, averages, minimums, and maximums for an orders table.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.
A3.2
Produce grouped reports at three different grains and write a sentence defining what one row represents in each.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.
A3.3
Use WHERE and HAVING together to find strong categories within a recent date range.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.
A3.4
Build a CASE-based report that places values into named bands and calculates counts and percentages for each band.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.

Chapter project

School Performance Dashboard

School Performance Dashboard — From student, course, and assessment data, create reports for class size, score distribution, averages, passing rates, missing work, and improvement bands. Include at least one conditional aggregate and document how NULL scores are treated.

Required process

Write a short specification listing inputs, outputs, rules, and failure cases.
Break the work into small functions, queries, modules, or classes appropriate to the language.
Build the smallest working version before adding optional features.
Test normal, boundary, empty, and invalid cases and record the results.
Refactor names and duplication, then write a concise user guide.

Key terms

aggregate
grain
group
COUNT
SUM
AVG
HAVING
CASE
conditional aggregation