SQL GROUP BY Explained (Totals per Category)
GROUP BY turns many rows into one row per group. Get sales and order counts per order status, then join customers to get total sales per region, on a sample online shop database.
Step by step
One row per group
- Totals per group. Managers rarely want one big total. They want totals per region, per product or per month. In SQL, GROUP BY gives you that. Here we build one on a sample online shop database.
- One big total. Start with one number: SELECT SUM(order_total) AS sales FROM orders; adds up the order_total of every order. It returns 1 row: 15,322. Useful, but it hides everything inside it.
SELECT SUM(order_total) AS sales FROM orders;
GROUP BY
- Pick the group. To split the total by order status, add status to the select list, before the sum, so it reads SELECT status, SUM(order_total) AS sales. The query isn't finished yet.
SELECT status, - GROUP BY. Then add GROUP BY status at the end. GROUP BY makes one row for each status, and SUM adds up only the orders in that group. Run it and you get 4 rows, one for each status.
GROUP BY status; - Sales per status. Each number in the sales column is the sales for one status. Delivered is by far the biggest.
Count as well
- Add a count. You can have more than one total. Add COUNT(*) AS orders before the sum. COUNT(*) counts the rows in each group. Run it again. Each of the 4 statuses now shows how many orders it has and how much they were worth.
COUNT(*) AS orders, SUM(order_total) AS sales
Sales per region
- Per region. For sales per region: the region is stored on the customer, not the order. So join customers to orders on customer_id, select c.region with SUM(o.order_total), and GROUP BY c.region. Run it and you get 4 regions with 4 totals.
SELECT c.region, SUM(o.order_total) AS sales FROM orders AS o JOIN customers AS c ON c.customer_id = o.customer_id GROUP BY c.region; - The top region. North sold the most, over 5,300. That's a number you can put straight in a report.
Recap
- The GROUP BY rule. GROUP BY makes one row per group. Every column in the select list must either be in the GROUP BY or sit inside a total like SUM or COUNT.
Common mistakes
- Selecting a column that isn't grouped. SELECT status, customer_id ... GROUP BY status gives an error in most databases. Add the column to GROUP BY or wrap it in SUM or COUNT. Some setups, like SQLite, allow it but return a value from one random row.
- Filtering totals with WHERE. WHERE filters rows before they are grouped, so WHERE SUM(order_total) > 1000 fails. Use HAVING after GROUP BY, like HAVING SUM(order_total) > 1000.
- Counting a column instead of rows. COUNT(*) counts every row in the group. COUNT(order_total) skips rows where order_total is empty (NULL), so the two can give different counts.
- Grouping by the wrong table's column. After a join, say which table each column comes from, like c.region and o.order_total. If both tables have a column with the same name, leaving out the alias causes an error.
When a business analyst uses this
Before a sign-off meeting, a business analyst can run SELECT status, COUNT(*) AS items FROM requirements GROUP BY status; on the requirements log to show how many items are Approved, In review or Open, instead of counting rows by hand.
Questions people ask
Why do we use GROUP BY in SQL?
To get a total for each group instead of one total for the whole table. For example, it gives sales per region or the number of orders per status.
Can you use WHERE with GROUP BY?
Yes. WHERE goes before GROUP BY and filters rows before they are grouped. To filter on the totals themselves, use HAVING after GROUP BY.
Can you GROUP BY multiple columns?
Yes. GROUP BY c.region, o.status gives one row for each pair of region and status. Every column you select without a total must be in that list.
