GROUP BY

Split data into groups and aggregate each one separately

Intermediate 16 minGROUP BYaggregation

Grouping Rows

GROUP BY splits your data into groups and applies aggregate functions to each group separately. Without it, aggregates like COUNT and SUM operate on the entire table and return a single row. Here's how grouping works:

One row per group — GROUP BY category produces one output row per unique category. GROUP BY category, country produces one row per unique category-country combination, giving you finer granularity in the results.

The golden rule — Every column in your SELECT must either appear in GROUP BY or be wrapped inside an aggregate function. SQL cannot pick a single value to represent a group of rows — it needs to know how to collapse them.

Execution order matters — The full sequence is: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. This means WHERE filters individual rows *before* grouping, and HAVING filters groups *after* aggregation completes.

Knowledge Canvas

How GROUP BY Works

Collapse rows into groups

GROUP BY collects rows with matching values into a single group
Each group produces exactly one output row
Non-aggregated columns in SELECT MUST be in GROUP BY
Multiple columns = composite groups

Mental Model

Think of it as sorting into buckets

GROUP BY country → one box per country. GROUP BY country, city → one box per country+city combination.

Imagine physically sorting rows into labeled boxes
Each box = one unique combination of GROUP BY columns
Aggregates (COUNT, SUM, AVG) summarize what's inside each box
The result has one row per box

Execution Order

Where GROUP BY fits in the pipeline

FROM — pick the table(s)

WHERE — filter individual rows

GROUP BY — collapse rows into groups

HAVING — filter groups

SELECT — compute output columns

ORDER BY — sort the result

LIMIT — cap row count

GROUP BY Gotchas

The most common aggregation errors

Non-aggregated column not in GROUP BY → error (or random value in MySQL)
WHERE can't filter groups — use HAVING
GROUP BY alias doesn't work in all databases
NULL is treated as its own group

GROUP BY Rules

Memorize these

Every non-aggregate in SELECT must appear in GROUP BY
GROUP BY runs after WHERE, before HAVING
GROUP BY NULL = one group for all NULLs
Column order in GROUP BY affects composite grouping

Syntax

Syntax Template

1SELECT

2 group_col,

3 AGG(value_col)

4FROM table

5GROUP BY

6 group_col;

GROUP BY colOne output row per unique value of col

Multiple columnsGROUP BY col1, col2 — one row per unique combination

RuleNon-aggregated columns in SELECT must be in GROUP BY

Sample Data

products

10 rows

name	category	price
Wireless Mouse	Electronics	24.99
Office Desk	Furniture	189
LED Desk Lamp	Furniture	45.5
USB-C Cable	Electronics	12.99
Bluetooth Speaker	Electronics	79
Office Chair	Furniture	95
Coffee Maker	Appliances	89.99
Notebook Set	Stationery	8.5
Wireless Headphones	Electronics	159
Standing Desk	Furniture	425

orders

12 rows

id	status	total
5001	delivered	59.98
5002	delivered	449
5003	shipped	12.5
5004	delivered	89.99
5005	pending	245
5006	delivered	159
5007	shipped	45.5
5008	cancelled	79
5009	delivered	425
5010	pending	18.99
5011	delivered	134
5012	cancelled	65.5

Worked Example

Find the number of products and average price for each category.

SQL

1SELECT

2 category,

3 COUNT(*) AS product_count,

4 ROUND(AVG(price), 2) AS avg_price

5FROM products

6GROUP BY

7 category

8ORDER BY

9 avg_price DESC;

GROUP BY category creates four groups: Furniture (4 products, avg 188.63), Appliances (1 product, avg 89.99), Electronics (4 products, avg 69.00), Stationery (1 product, avg 8.50). COUNT and AVG are computed separately within each group. The ORDER BY sorts the results by average price.

Output

3 rows

category	product_count	avg_price
Furniture	4	188.63
Appliances	1	89.99
Electronics	4	69

Common Mistakes

❌

Selecting ungrouped columns

SQL

1SELECT

2 name,

3 category,

4 COUNT(*)

5FROM products

6GROUP BY

7 category;

Which 'name' should SQL show for the Electronics group? There are 4 products — it's ambiguous. Some databases will error; others will pick an arbitrary row.

Only include columns that are in GROUP BY or inside aggregate functions.

Key Concepts

1Every non-aggregated SELECT column must appear in GROUP BY

2WHERE filters rows BEFORE grouping — HAVING filters AFTER

3GROUP BY col1, col2 creates one row per unique combination

4Execution: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

Pro Tip

GROUP BY is how you go from "one total for everything" to "one total per category/customer/month." It's the backbone of analytical SQL.

When to Use

Revenue by product category, orders per customer, average rating per product, monthly sales totals, users by country.

Challenge

Solve the problem below

Find the number of orders and total revenue for each order status from the orders table. Sort by total revenue descending.

Your Query

MIN & MAX

HAVING