GROUP BY in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this Aggregate Functions (COUNT, SUM, AVG, MIN, MAX)
Builds toward HAVING, Derived Tables (Subqueries in FROM), Conditional Aggregation (CASE inside Aggregates), Window Functions Introduction (OVER, PARTITION BY)
What is GROUP BY in SQL?
GROUP BY divides your result into groups and runs an aggregate function on each group separately.
You're building a sales report and the data is already in SQL. You don't want one total. You want revenue broken down by order status. How much came from delivered orders? How much is still pending? That kind of breakdown is exactly what GROUP BY is for.
Without GROUP BY, an aggregate function like COUNT(*) or SUM() collapses the whole table into a single number. GROUP BY changes that: instead of one number for everything, you get one number per group.
How do you write a GROUP BY query in SQL?
Think of it like sorting a stack of receipts into piles before you start counting. One pile per status, one pile per customer. Once sorted, each pile gets its own total. Here's what that looks like:
SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY statusSQL partitions the orders table into groups, one per distinct status value, then counts the rows in each group. The result has one row per status, not one row per order.
You can use any aggregate function alongside GROUP BY. Revenue per status instead of a count:
SELECT status, SUM(total_amount) AS total_revenue
FROM orders
GROUP BY statusThe column you're grouping by appears in SELECT and in GROUP BY. That's the pattern.
Can you use WHERE with GROUP BY in SQL?
GROUP BY works with WHERE. SQL filters first, then groups and aggregates what survives:
SELECT customer_id, COUNT(*) AS delivered_order_count
FROM orders
WHERE status = 'delivered'
GROUP BY customer_idOnly delivered orders reach the grouping step. The result shows a count per customer, but only for their delivered orders.
Why must every SELECT column appear in GROUP BY?
The one thing that trips people up: every column in SELECT must either appear in the GROUP BY clause or be wrapped in an aggregate function.
When you group by status, a single status = 'delivered' group can contain orders from dozens of different customers. SQL has no basis for deciding which customer_id to show you, so it refuses the query. That's the right behavior:
SELECT customer_id, status, COUNT(*) FROM orders GROUP BY status
The error identifies exactly which column caused the problem. Fix it by adding customer_id to GROUP BY, giving you one row per customer-status pair, or by dropping customer_id from SELECT if you don't need it.
You write: SELECT category_id, name, COUNT(*) FROM products GROUP BY category_id. What happens?
Practice GROUP BY in SQL
Brightlane's fulfilment director is reviewing pipeline health ahead of the monthly board report.
Write a query to return the number of orders in each status.
Assumptions:
- The
orderstable contains every order Brightlane has processed. - The
statuscolumn has a small handful of values; the result will have one row per status.
Output:
- One row per
statusvalue, with columnsstatusandorder_count.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solutionStation Zero, our free browser SQL game, teaches this concept inside a story. No signup.
aggregate the station logs in Station Zero9 GROUP BY practice problems
Write a query to return the number of orders in each status.
Write a query to return each status and its total order value.
Write a query to return each department_id and the number of employees assigned to it.
Write a query to return each customer's ID alongside the count of their delivered orders.
Write a query to return each unique customer-status pairing and the number of orders that fall into it.
Write a query to return each product's ID, the total units sold, and the total revenue across all order line items.
Write a query to return each status and the number of unique customers who have placed at least one order in that status.
Write a query to return each category_id alongside the number of products assigned to it.
Write a query to return each customer's ID alongside their total spend across non-cancelled orders.
Start learning to practice all 9 GROUP BY problems, with instant grading and mastery tracking.
Deeper guides on GROUP BY
- grouping before you pivot
One aggregate with a FILTER clause per column, with no extension to install.
Common questions about GROUP BY
Does GROUP BY sort the result?
Not reliably. Grouped output often arrives in an order that looks deliberate, but nothing promises it and it can change with the data or the plan. Add ORDER BY whenever the sequence is part of the answer.
Can you group by more than one column?
Yes. List them after GROUP BY separated by commas and you get one row per distinct combination, so grouping by country and status gives a row for each pairing that actually occurs rather than for every pairing that could.
What happens to NULLs in a grouped column?
They collect into a single group of their own. Every row missing a value ends up in one output row where that column is NULL, which is easy to skim past when you are reading a long result and expecting only real categories.
Can you use GROUP BY without an aggregate?
Yes, and it behaves like SELECT DISTINCT: one row per group and no summary. It is valid SQL, though saying DISTINCT usually states the intent more plainly than grouping and then aggregating nothing.
