Aggregation and GROUP BY

⏱ 30 min

Hook

The merchandising manager stops by your desk: "Forget the individual orders — just tell me how much revenue each product category brought in last month, and which ones cleared our $100 reporting threshold." You already know how to pull rows. But the manager does not want rows; they want one number per category. Handing over three thousand order lines would be worse than useless. You need to collapse those rows into a summary.

Concept

Filtering and sorting return the rows you started with, just fewer of them. Aggregation is different: it collapses many rows into a single summary value. An aggregate function takes a whole column and returns one number.

Function	Answers
COUNT(*)	How many rows?
SUM(amount)	What is the total?
AVG(amount)	What is the average?
MIN / MAX	What is the smallest / largest?

On their own, these squash the entire table into one row: SELECT SUM(amount) FROM orders gives you total revenue across everything. That is rarely the question. The manager wants revenue per category — one summary for each group. That is what GROUP BY does: it splits the rows into groups by a column's value, then runs the aggregate once per group.

SELECT category, SUM(amount) AS revenue
FROM orders
GROUP BY category;

The mental model: GROUP BY category sorts every row into buckets — one bucket per distinct category — and SUM(amount) totals each bucket separately. The result has one row per bucket. This gives you the rule that trips up everyone at first: every column in your SELECT must either be inside an aggregate function or named in GROUP BY. If you group by category and also select order_id, the database cannot answer "which order id?" for a bucket of two hundred orders — so it refuses.

Two more pieces complete the picture. AS revenue renames the output column to something a reader understands — an alias. And when you need to filter on the summary itself ("only categories over $100"), you cannot use WHERE, because WHERE ran before the groups existed. You use HAVING, which filters groups after aggregation:

SELECT category, SUM(amount) AS revenue
FROM orders
GROUP BY category
HAVING SUM(amount) > 100;

The distinction is worth memorizing because it is asked in every SQL interview: WHERE filters rows before grouping; HAVING filters groups after.

🧠 Knowledge check

1. A report needs total revenue for each region. Which clause creates one summary row per region?

a) ORDER BY regionb) GROUP BY regionc) WHERE region IS NOT NULLd) SELECT DISTINCT region

2. You want only categories whose total revenue exceeds $1,000. Where does that filter go?

a) WHERE SUM(amount) > 1000b) HAVING SUM(amount) > 1000c) ORDER BY SUM(amount) > 1000d) SELECT amount > 1000

Worked example

Let me answer the merchandising manager's question exactly as asked: revenue and order count per category, showing only the categories that cleared the $100 threshold. There is one wrinkle a careful analyst catches — cancelled orders never brought in money, so they must be excluded before totalling. That exclusion is a row filter, so it belongs in WHERE, before grouping.

So I have two filters doing two different jobs. WHERE status <> 'cancelled' throws out cancelled rows first (<> means "not equal"). Then GROUP BY category buckets what remains. Then HAVING SUM(amount) > 100 keeps only the buckets over threshold. I sort by revenue so the biggest category leads, with category name as a tiebreaker so the order is stable.

SELECT category,
       COUNT(*) AS order_count,
       ROUND(SUM(amount), 2) AS revenue
FROM orders
WHERE status <> 'cancelled'
GROUP BY category
HAVING SUM(amount) > 100
ORDER BY revenue DESC, category;

category     order_count  revenue
electronics  3            410.0
home         2            410.0

The books category is gone from the result — not because of an error, but because its non-cancelled revenue was only $55, below the$100 threshold, so HAVING filtered it out. Note the honest detail: electronics and home tie at $410. The tiebreaker in `ORDER BY` puts electronics first alphabetically, but I would tell the manager plainly that the two are tied rather than implying electronics "won." `ROUND(..., 2)` keeps the money readable to two decimals. The one-line answer: "Two categories cleared the threshold — electronics and home, tied at$410 — and books fell short at $55."

Hands-on

Your turn. The exercise below loads the same store's orders table for a different stakeholder. The finance analyst wants the average order value for each order status (pending, shipped, cancelled), rounded to two decimals and listed highest average first, so they can see whether shipped orders skew larger than pending ones. Write the aggregation query.

▶ SQL Kingdomda101-aggregation-avg-order-value-by-status

Practice in SQL Kingdom ↗

Success criteria: one row per status, an avg_value column rounded to two decimals, sorted descending by that average. SQL Kingdom validates your grouped result and awards XP on a match.

Recap

• You can collapse many rows into decision-ready numbers with COUNT, SUM, AVG, MIN, and MAX.
• You can produce one summary per group with GROUP BY, and you know every selected column must be aggregated or grouped.
• You can filter on the summary itself with HAVING, and you can explain why that is different from WHERE.

Next up: every summary so far came from one table. Real questions span two — "revenue by region" needs orders joined to customers. That is joins for analysts.

🧠 Knowledge check

1. In `SELECT category, order_id, SUM(amount) FROM orders GROUP BY category`, why does the database reject it?

a) SUM cannot be used with GROUP BYb) order_id is neither aggregated nor in GROUP BY, so it is ambiguous per groupc) category must come after SUMd) GROUP BY requires a HAVING clause

2. Cancelled orders should be excluded from a revenue-by-category summary. Where does that belong?

a) In HAVING, after groupingb) In WHERE, before groupingc) In ORDER BYd) It cannot be done in one query

📌 Key takeaways

• Aggregate functions (COUNT, SUM, AVG, MIN, MAX) collapse a column into one number.
• GROUP BY produces one summary row per group; every selected column is aggregated or grouped.
• WHERE filters rows before grouping; HAVING filters group summaries after.