HumachLearnLearn · explore · apply
Start learning
SQL for Analytics · Lesson 35

Group by and Aggregate Functions

GROUP BY changes SQL row grain by collecting rows with the same grouping key and returning one result row per group.

ConceptWorked examplePracticeKnowledge check
Textbook walkthrough

Group by and Aggregate Functions

GROUP BY changes SQL row grain by collecting rows with the same grouping key and returning one result row per group. Aggregate functions such as COUNT, SUM, AVG, MIN and MAX then summarise values inside each group. Every selected expression must either define the group or be aggregated, otherwise the query is conceptually ambiguous.

Learning goal: explain why Group by and Aggregate Functions behaves this way, apply it to a small example, and verify the result independently. Begin by being able to justify this first step: Start from the desired output grain, such as one row per region.

Deeper walkthrough

Read Group by and Aggregate Functions as a mechanism, not a recipe

Treat this as a sequence of observable decisions rather than one opaque command. Stage 1: Start from the desired output grain, such as one row per region. Stage 2: Choose grouping key columns. Stage 3: Apply aggregate functions to measures. Final checkpoint: Check group counts and totals against the ungrouped table.

Mechanism

Follow the transformation

Start from the desired output grain, such as one row per region.

Choose grouping key columns.

Apply aggregate functions to measures.

Evidence

Know what would convince you

  • Recompute one result from a handful of source rows or an independent formula.
  • Check row counts, group totals and units before interpreting differences.
Useful distinctionWHERE: Filters raw rows before aggregation.
Visual demonstration of Group by and Aggregate Functions
Visual demonstration: use the diagram to trace the main objects and state changes involved in Group by and Aggregate Functions.
Click a stage to inspect what happens, what changes, and what should be checked before moving on.
Stage 1

Start from the desired output grain

Start from the desired output grain, such as one row per region. Treat the output from Group by and Aggregate Functions as evidence to inspect: confirm its type, shape, range or units and connect it back to the input that produced it.

Output focus: inspect both the value and its shape/type/meaning before treating it as a trustworthy result.
How it works

Trace the mechanism step by step

  1. Start from the desired output grain, such as one row per region.
  2. Choose grouping key columns.
  3. Apply aggregate functions to measures.
  4. Use WHERE for row filtering before grouping and HAVING for conditions on aggregate results.
  5. Check group counts and totals against the ungrouped table.
Worked demonstration

Grouped SQL

-- Step 1 — Choose the output fields/expressions that the query should return.
SELECT region, COUNT(*) AS n, AVG(revenue) AS avg_revenue
-- Step 2 — Identify the source table or intermediate relation that supplies rows.
FROM sales
-- Step 3 — Create groups that aggregation functions will summarise.
GROUP BY region
-- Step 4 — Sort the final result into a deliberate presentation order.
ORDER BY avg_revenue DESC;
Expected / illustrative result
The result has one row per region instead of one row per sale.
Interpret the result.

For Group by and Aggregate Functions, trace one source row through the query and explain why it appears, disappears, duplicates or receives its calculated value.

Distinctions & related ideas

Place the concept correctly

WHEREFilters raw rows before aggregation.
GROUP BYDefines one output group per unique key combination.
HAVINGFilters groups after aggregate values exist.
Use deliberately

When it is appropriate

Use Group by and Aggregate Functions when it answers a defined question in SQL for Analytics and its inputs/assumptions match the current data or program state.

Boundary conditions

When to stop or reconsider

Reconsider Group by and Aggregate Functions when the required information is unavailable, the operation would violate a validation/data boundary, or a simpler operation answers the question more transparently.

Common mistakes

Failure modes to recognise

  • Changing the population/grain without noticing it.
  • Using an undefined denominator, time window, unit or category rule.
  • Presenting a number/plot without reconciling it to source counts or totals.
Verification

How to check the result

  • Recompute one result from a handful of source rows or an independent formula.
  • Check row counts, group totals and units before interpreting differences.
  • Change one source value and predict which reported value/mark should change.
Hands-on practice

Demonstrate understanding

Try this:

Construct a tiny example of Group by and Aggregate Functions. First start from the desired output grain, such as one row per region. Then choose grouping key columns. Predict the result before execution and explain one boundary or failure case.

Use tiny tables where every row can be enumerated. State output grain first, then reconcile each output row to the source.
Knowledge check

Check reasoning, not memorisation

Which approach best demonstrates understanding of Group by and Aggregate Functions?

Quick reference

Remember the logic

Step 1Start from the desired output grain, such as one row per region.
Step 2Choose grouping key columns.
Step 3Apply aggregate functions to measures.
Step 4Use WHERE for row filtering before grouping and HAVING for conditions on aggregate results.
Lesson summary

What to remember

  • GROUP BY changes SQL row grain by collecting rows with the same grouping key and returning one result row per group. Aggregate functions such as COUNT, SUM, AVG, MIN and MAX then summarise values inside each group. Every selected expression must either define the group or be aggregated, otherwise the query is conceptually ambiguous.
  • Start from the desired output grain, such as one row per region.
  • Changing the population/grain without noticing it.
  • Recompute one result from a handful of source rows or an independent formula.