SQL Mastery
Intermediate 15 min read SQL Mastery

Aggregations and GROUP BY

Collapse rows without collapsing their meaning

GROUP BY collapses rows into groups and aggregate functions summarize them. HAVING filters groups, and the grain of the group defines the meaning of every aggregate.

GROUP BYHAVINGCOUNTSUMAVGROLLUPgrain
Prerequisites: Joins In Depth
Before we start

What you will learn

  • Write correct GROUP BY + HAVING queries
  • Explain why SELECT columns must be grouped or aggregated
  • Use ROLLUP/CUBE for hierarchical totals
Introduction

What is this?

GROUP BY splits rows into groups sharing the listed columns; aggregate functions (COUNT, SUM, AVG, MIN, MAX) produce one row per group. HAVING filters whole groups after aggregation. The grouping columns define the grain of the output — the single most important thing about an aggregate query.

Motivation

Why do you need it?

  • Every dashboard metric is a GROUP BY query; getting the grain wrong mislabels the number it reports.
  • HAVING vs WHERE is a common confusion that yields seemingly-anomalous results.
  • ROLLUP/CUBE add subtotal rows that reports and expense cubes need.
Think about it

Mental model

Imagine

Sorting coins into jars by denomination

Think of

GROUP BY

Each jar holds one denomination (a group); the count in each jar is the aggregate. A jar's label (the grouping column) IS the grain — if two jars share a label, your grouping was wrong.

Mechanics

How it works

  • Grouping keys must appear in SELECT either verbatim or as aggregates.
  • Aggregates (SUM/COUNT/AVG) compute per group; COUNT(*) counts rows, COUNT(col) counts non-NULL values.
  • HAVING filters groups after aggregation; WHERE filtered rows before grouping.
  • GROUP BY a, b: unique (a, b) pairs become groups — grain is the pair.
  • ROLLUP (a, b) adds (a, total) and (grand-total) rows; CUBE adds all combinations.

WHERE vs HAVING in the query pipeline.

Rows
WHERE
GROUP BY
HAVING
SELECT aggregates
Hands-on

Examples

Grouped revenue with a group filter

Grouped revenue with a group filter
SELECT region, SUM(amount) AS revenue
FROM sales
WHERE ts >= '2026-01-01'
GROUP BY region
HAVING SUM(amount) > 100
ORDER BY revenue DESC;

WHERE trims pre-group rows; HAVING drops regions whose post-group total is small.

Real world

In production

  • A daily KPI table computes revenue by channel/day with GROUP BY channel, day — the grain a dashboard trusts.
  • A sales cube builds ROLLUP rows so BI can pivot to region and grand totals without extra queries.
  • A quality gate counts DISTINCT keys after an upsert to prove no duplicates creep in.
Guidelines

Best practices

  • State the grouping grain in the model's documentation before writing the query.
  • Use HAVING for group-level filters; keep row filters in WHERE for performance.
  • COUNT(DISTINCT key) to assert uniqueness; COUNT(*) to assert row volume.
  • Prefer SUM on numeric typed columns; SUM over text or NULL-rich data misleads.
Watch out

Common mistakes

  • SELECTing a non-grouped, non-aggregated column — allowed in some dialects, silently wrong data in all.
  • Filtering groups in WHERE instead of HAVING, so the aggregates are already wrong before they reach the report.
  • Mixing COUNT(*) with COUNT(col): dropping NULLs undercounts.
Root cause analysis

Common issues

Error
Dashboard total differs from source sum
Root cause

A grouping grain (e.g. by day-hour while source is by-minute) collapsed differently

Fix

Align the reporting grain with the fact's grain or document the intended rollup.

Error
HAVING effectively didn't filter
Root cause

Filter applied to a NULL-laden COALESCE that passes despite the spirit

Fix

Filter on the raw column, then COALESCE only in SELECT.

When things break

Debugging tips

  • When a total doesn't reconcile, re-read the grouping keys and add the missing grain.
  • Run a COUNT(*) per group to sanity-check group sizes before trusting aggregates.
  • If HAVING seems to ignore rules, confirm you aren't masking with COALESCE in the same expression.
Interviews

Interview checkpoint

Practice

Hands-on lab

Build a Daily KPI Table with Correct Grain
25 min · intermediate
In progress
Objectives
  • Aggregate sales into a daily-by-channel KPI table
  • Use HAVING to drop under-performing groups
  • Add ROLLUP subtotal rows

Build a Daily KPI Table with Correct Grain

25 min · intermediate

0/4 steps
0%
sandbox · Linux sandbox
$
Try these commands
Test yourself

Quick quiz

0/2
1

SELECT region, SUM(amount) FROM sales GROUP BY region — what is the row grain?

2

HAVING can reference an aggregate like SUM(amount); WHERE cannot.

Go deeper

Official documentation

PostgreSQL aggregate functions
docs
SQLite aggregate functions
docs