Learn / SQL Fundamentals / Aggregation & GROUP BY
Lesson 4 of 6

Aggregation & GROUP BY

"How much did Priya spend in total?" isn't a question about any one row in the orders table — it's a question about a whole group of rows, boiled down to a single number. SQL's aggregate functions, paired with GROUP BY, exist for exactly this: turning many rows into summary answers.

💡 The Bottom Line

Aggregate functions like COUNT, SUM, AVG, MIN, and MAX collapse many rows into one number. GROUP BY runs that collapse separately for each distinct value of a column — "one total per customer" instead of one grand total. Filter the raw rows with WHERE before grouping; filter the grouped results with HAVING after.

Aggregate functions on their own

Using the orders table from last lesson:

SELECT COUNT(*) FROM orders;        -- how many orders exist
SELECT SUM(total) FROM orders;      -- total revenue across all orders
SELECT AVG(total) FROM orders;      -- average order value
SELECT MIN(total) FROM orders;      -- cheapest order
SELECT MAX(total) FROM orders;      -- priciest order

Each of these collapses the entire table down to a single row with a single value — useful, but not yet broken down per customer.

GROUP BY: one answer per group

GROUP BY tells the database "run the aggregate separately for each distinct value of this column" — one total per customer, instead of one grand total:

SELECT customer_id, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id;
customer_id | total_spent
------------+------------
1           | 60.50
2           | 99.00

Combine it with the JOIN from last lesson to get names instead of raw IDs:

SELECT customers.name, COUNT(orders.id) AS order_count, SUM(orders.total) AS total_spent
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.name;

🙋 There Are No Dumb Questions

Q: Why do I need GROUP BY at all — can't I just filter with WHERE for each customer?
A: You could run one query per customer with WHERE customer_id = 1, then another with = 2, and so on — but that's one query per group, and you'd need to know every customer_id in advance. GROUP BY does all of those totals in a single query, automatically, for however many distinct customers exist.

WHERE vs. HAVING

WHERE filters individual rows before grouping happens. HAVING filters groups, after the aggregate has been computed — so it can reference things like SUM(total), which WHERE can't:

-- only customers who have spent more than 50 in total
SELECT customers.name, SUM(orders.total) AS total_spent
FROM customers
JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.name
HAVING SUM(orders.total) > 50;

⚠️ Watch Out

Trying to write WHERE SUM(total) > 50 is a common mistake and most databases will reject it outright — at the point WHERE runs, the rows haven't been grouped yet, so there's no sum to compare against. Aggregate conditions always belong in HAVING, which runs after grouping.

🔧 Try It Yourself

Using customers and orders, write a query that lists each customer's name and their order count, but only includes customers who have placed 2 or more orders. Think carefully about whether that condition belongs in WHERE or HAVING.

✅ Quick Check

Which clause would you use to keep only groups where COUNT(*) > 1?

Next up: as your tables grow to millions of rows, queries that scan every row start to slow down — indexes are how you speed lookups back up.

← Back to
Next →