Aggregating rows
So far each query returned rows much like the ones stored. Aggregation is different: it collapses many rows into one summary number. This is where raw data starts turning into information - "how many orders," "total revenue," "average order value."
The aggregate functions you will use constantly are COUNT, SUM, AVG, MIN, and MAX. On their own they fold a whole table into a single row:
That answers "across all orders, how many, what total, what average." One row out, no matter how many rows in.
GROUP BY: one summary per group
Usually you do not want one number for the whole table - you want one number per customer, per country, per month. GROUP BY splits the rows into groups and produces one summary row for each.
The rule that confuses beginners: every column in SELECT must either be in the GROUP BY or be wrapped in an aggregate. That is not pedantry - if you grouped by customer_id, the database has many total values per group and no way to show just one, so you must tell it how to combine them (SUM, AVG, ...). AS simply renames a column in the output.
Grouping and aggregating is concept #4 in Codewars' Would You Pass the Google SQL Interview? - folding raw rows into per-day, per-store, per-month statistics is the bread and butter of analytics questions.
WHERE versus HAVING
Both filter, but at different moments - and the evaluation order from the last lesson tells you which to reach for. WHERE filters rows before grouping. HAVING filters groups after the aggregate is computed.
You cannot put SUM(total) > 50 in WHERE, because when WHERE runs the sum does not exist yet. That single fact is why HAVING has to exist at all.
MIN and MAX: the extremes
MIN and MAX return the smallest and largest value in a group - the cheapest order, the latest date, the highest spender:
They work on text and dates too (MAX(created_at) is the most recent order).
Counting, and how NULL changes the answer
COUNT has three forms, and the difference is all about NULL:
COUNT(*)counts rows - it never skips anything.COUNT(col)counts rows wherecolis not NULL - missing values are ignored.COUNT(DISTINCT col)counts the distinct non-NULL values.
Eve has no country, so COUNT(country) is one less than COUNT(*), and COUNT(DISTINCT country) collapses IE, LT to just two:
This NULL-skipping is not unique to COUNT: SUM and AVG ignore NULLs too. AVG divides by the count of non-NULL values, not by the row count - so a column full of gaps still averages cleanly, but be sure that is the average you meant.
Grouping by more than one column
GROUP BY can take several columns - one group per combination of their values. "How many customers per country, split by... " - here, count customers per country, ordered:
To group on a true combination, list the columns: GROUP BY country, <another column> makes one row per distinct pair. The same rule still holds - every non-aggregated SELECT column must appear in the GROUP BY.
Mean versus median
AVG gives the mean - the sum divided by the count. It is the right centre for symmetric data, but a single outlier drags it far from "typical". The median - the middle value once sorted - ignores how extreme the outliers are, so it survives skew.
One boss's pay skews the mean above every junior; the median does not move:
The mean of 118 sits above four of the five people; the median of 50 is a real, typical pay. Rule of thumb straight from the interview circuit: outliers or skew → prefer the median; clean, symmetric data → the mean is fine.
Mean vs. median is concept #7 in Codewars' Would You Pass the Google SQL Interview? - a statistics question wearing a SQL costume, testing whether you pick a centre that resists outliers.
Standard SQL has no MEDIAN function. PostgreSQL computes it with PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY pay); SQLite has neither, so the query above fakes it with ORDER BY + LIMIT/OFFSET to grab the middle one or two rows. AVG (the mean) is universal.
Exercise
Worked, then half-filled, then yours. Use the sandbox.
1. Worked. Count how many customers are in each country (the query above).
2. Finish it. For each customer, show their total spend. Fill the blanks, then run - the box checks your result.
3. Write it yourself. Show only the customers whose total spend is above 30.00, with that total. Run it to check your answer.
Show answers
2. SELECT customer_id, SUM(total) AS spent FROM orders GROUP BY customer_id;
3.
SELECT customer_id, SUM(total) AS spent
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 30.00;
Quick quiz
Aggregating rows
5 questions1In a query with GROUP BY, which columns may appear bare in the SELECT list?
2What is the difference between WHERE and HAVING?
3The customers table has 6 rows; Eve's country is NULL. What does COUNT(country) return?
4How does AVG treat NULL values in its column?
5What does COUNT(DISTINCT country) return for the customers table (countries IE, IE, LT, LT, NULL, IE)?