SQL GROUP BY explained with simple examples

How do you get one row per customer, or a total for each category, out of a table full of individual orders?

Short answer: GROUP BY collapses rows that share a value into one row per group. You pair it with an aggregate function like COUNT, SUM or AVG, which calculates one number for each group.

SELECT category, COUNT(*)
FROM orders
GROUP BY category;

By the end of this post you will be able to count and total rows per group, group by more than one column, filter groups with HAVING, and fix the error everyone hits the first time. If SELECT and WHERE are new, start with SQL for beginners: the 10 queries to learn first.

The example table

Every query in this post uses this small orders table:

id	customer	category	amount
1	Ana	Books	20
2	Ben	Games	60
3	Ana	Games	40
4	Cleo	Books	15
5	Ben	Books	25
6	Ana	Music	10

Six rows, three customers, three categories. Small enough to check every answer by hand.

Your first GROUP BY

How many orders are there in each category?

SELECT category, COUNT(*) AS orders
FROM orders
GROUP BY category;

category	orders
Books	3
Games	2
Music	1

Here is what happened. GROUP BY category sorted the six rows into three piles: one for Books, one for Games, one for Music. Then COUNT(*) counted the rows in each pile. You get one result row per pile.

That picture is worth keeping in your head: group first, then calculate one value per group. Grouping Rows With Group By (opens in a new tab) walks through the same idea.

Totals and averages per group

COUNT is one aggregate function. The others work the same way:

Function	What it gives you per group
`COUNT(*)`	Number of rows
`SUM(column)`	Total of the values
`AVG(column)`	Average of the values
`MIN(column)`	Smallest value
`MAX(column)`	Largest value

How much has each customer spent?

SELECT customer, SUM(amount) AS total_spent
FROM orders
GROUP BY customer;

customer	total_spent
Ana	70
Ben	85
Cleo	15

You can use several aggregates in one query:

SELECT category,
       COUNT(*) AS orders,
       SUM(amount) AS revenue,
       AVG(amount) AS average_order
FROM orders
GROUP BY category;

category	orders	revenue	average_order
Books	3	60	20
Games	2	100	50
Music	1	10	10

The AS names make the result readable. Totals Averages And Extremes (opens in a new tab) covers each function.

Grouping by two columns

List more than one column in GROUP BY and you get one row for each unique combination:

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

Ana appears three times, once each for Books, Games and Music, because she has orders in all three. Ben appears twice. Cleo once. Each row is one customer-and-category pair.

Filtering groups with HAVING

Which customers have spent more than 50? You can't use WHERE for this, because WHERE looks at individual rows, and the total doesn't exist until the rows are grouped. That is what HAVING is for:

SELECT customer, SUM(amount) AS total_spent
FROM orders
GROUP BY customer
HAVING SUM(amount) > 50;

customer	total_spent
Ana	70
Ben	85

Cleo's group totals 15, so HAVING drops it.

You can use both in one query. WHERE filters rows before grouping, and HAVING filters groups after:

SELECT customer, SUM(amount) AS total_spent
FROM orders
WHERE amount > 15
GROUP BY customer;

customer	total_spent
Ana	60
Ben	85

WHERE amount > 15 removed the 15 and 10 orders first. Ana's total dropped to 60, and Cleo disappeared completely because she had no orders left to group. See Filtering Groups With Having (opens in a new tab).

Sorting the result

Add ORDER BY to rank the groups. Who are the top spenders?

SELECT customer, SUM(amount) AS total_spent
FROM orders
GROUP BY customer
ORDER BY total_spent DESC
LIMIT 2;

customer	total_spent
Ben	85
Ana	70

Ranking A Grouped Result (opens in a new tab) practices exactly this.

The order SQL runs it in

You write a query in one order, but the database works through it in another. Knowing that order explains most GROUP BY rules:

FROM picks the table.
WHERE keeps the rows you want.
GROUP BY puts the rows into groups.
HAVING keeps the groups you want.
SELECT works out the columns.
ORDER BY sorts the result.
LIMIT keeps the first few rows.

That is why WHERE can't see a SUM: the groups don't exist yet at step 2. And it is why ORDER BY can use the total_spent alias: by step 6, SELECT has already named it.

The classic GROUP BY error

Sooner or later you will write something like this:

SELECT customer, amount
FROM orders
GROUP BY customer;

PostgreSQL stops you:

ERROR: column "orders.amount" must appear in the GROUP BY clause or be used in an aggregate function

The error makes sense once you picture the piles. Ana's group has three amounts: 20, 40 and 10. The result has room for one row for Ana, so which amount should it show? SQL won't guess.

The fix is to say what you want from the group:

SELECT customer, SUM(amount) AS total_spent
FROM orders
GROUP BY customer;

The rule: every column in SELECT must either be in GROUP BY or be inside an aggregate function. Some databases, like SQLite, let the first query run and quietly pick one of the values. That doesn't make it correct. It just hides the problem.

GROUP BY with a JOIN

Real data is usually spread across tables. If customer names live in a customers table, you join first, then group:

SELECT customers.name, COUNT(*) AS orders
FROM orders
JOIN customers ON customers.id = orders.customer_id
GROUP BY customers.name;

The join builds the combined rows, and GROUP BY collapses them, in that order. SQL joins explained with simple examples covers the join half, and Grouping A Joined Result (opens in a new tab) puts the two together.

Common mistakes

Selecting a column that isn't grouped or aggregated. Either add it to GROUP BY or wrap it in SUM, MAX or another aggregate.
Using WHERE to filter on an aggregate. WHERE SUM(amount) > 50 is an error. Use HAVING.
Using a SELECT alias in HAVING. HAVING total_spent > 50 works in some databases, like MySQL, but not in others, like PostgreSQL. Repeating SUM(amount) works everywhere.
Mixing up COUNT(*) and COUNT(column). COUNT(*) counts rows. COUNT(column) skips rows where that column is NULL.
Expecting the groups in a particular order. GROUP BY doesn't sort. Add ORDER BY if order matters.

Quick recap

GROUP BY collapses rows with the same value into one row per group.
Aggregate functions like COUNT, SUM and AVG calculate one value per group.
WHERE filters rows before grouping. HAVING filters groups after.
Every selected column must be grouped or aggregated.
Add ORDER BY and LIMIT to rank the groups.

Frequently asked questions

What does GROUP BY do in SQL?

It puts rows that share a value into groups, so an aggregate function can calculate one result per group. GROUP BY category with COUNT(*) gives you the number of rows in each category.

What is the difference between WHERE and HAVING?

WHERE filters individual rows before they are grouped. HAVING filters whole groups after grouping, so it can use aggregates like SUM(amount) > 50.

Can you GROUP BY multiple columns?

Yes. GROUP BY customer, category makes one group for each unique combination of customer and category.

Does GROUP BY sort the results?

Not reliably. Some databases happen to return groups in order, but SQL doesn't promise it. Use ORDER BY when the order matters.

Why do I get a "must appear in the GROUP BY clause" error?

You selected a column that has several values in each group, and SQL doesn't know which one to show. Add the column to GROUP BY, or wrap it in an aggregate function like SUM or MAX.