The GROUP BY Clause

What is GROUP BY?

GROUP BY groups rows that share the same value in a column, then lets you run an aggregate function — like COUNT, SUM, AVG, MIN, or MAX — on each group separately.

Without GROUP BY, aggregate functions collapse the entire table into one number. With GROUP BY, you get one number per group.

-- Total revenue for the whole table (one row back)
SELECT SUM(price_usd) FROM coffee_shop_sales;

-- Total revenue broken down per drink type (one row per drink)
SELECT drink_type, SUM(price_usd) AS total_revenue
FROM coffee_shop_sales
GROUP BY drink_type;

Aggregate functions with GROUP BY

You can use any aggregate function alongside GROUP BY. The function runs independently within each group.

-- Number of orders per shipping method
SELECT shipping_method, COUNT(*) AS order_count
FROM online_bookstore_orders
GROUP BY shipping_method;

-- Average workout duration per membership tier
SELECT membership_type, AVG(workout_duration_minutes) AS avg_duration
FROM gym_member_visits
GROUP BY membership_type;

-- Total nights stayed per room type
SELECT room_type, SUM(nights_stayed) AS total_nights
FROM hotel_room_bookings
GROUP BY room_type;

Using WHERE with GROUP BY

WHERE filters rows before grouping happens. This means you can narrow down which rows are included in the groups before any aggregation takes place.

-- Average price per drink type, but only for Large drinks
SELECT drink_type, AVG(price_usd) AS avg_price
FROM coffee_shop_sales
WHERE size = 'Large'
GROUP BY drink_type;

The clause order is always: SELECT … FROM … WHERE … GROUP BY.

Common mistakes

Selecting a non-grouped, non-aggregated column — every column in your SELECT must either appear in GROUP BY or be wrapped in an aggregate function. Writing SELECT drink_type, price_usd, SUM(...) GROUP BY drink_type is an error because price_usd is neither grouped nor aggregated.

Confusing WHERE and HAVING — WHERE filters rows before grouping; HAVING filters groups after aggregation. You can't use WHERE SUM(price_usd) > 500 — that requires HAVING, which is covered in the next topic.

Forgetting GROUP BY entirely — if you write SELECT drink_type, SUM(price_usd) without GROUP BY drink_type, most databases will return an error because drink_type is unaggregated.