GROUP BY: a report by groups

In short: GROUP BY puts rows with the same value into one group, so that COUNT, SUM or AVG are calculated for each group separately.

Problem

The warehouse dashboard

The warehouse wants a dashboard: how many orders are new, how many paid, how many shipped. With COUNT(*) and WHERE status = 'new' you would need five queries, one for each status. And if a sixth status appears tomorrow, the report will not show it.

You need one query that finds the statuses by itself and counts each one.

The idea

GROUP BY

GROUP BY column puts the rows with the same value in the column into one group. The aggregate function is worked out for each group separately:

SQLdatabase: shop

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

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

▶ RunRestore the queryFresh database

60 orders, five groups, five rows. Each group gives one row.

The customers by city:

SQLdatabase: shop

SELECT city, COUNT(*)
FROM customers
GROUP BY city;

SELECT city, COUNT(*) FROM customers GROUP BY city;

▶ RunRestore the queryFresh database

Boris has no city and is in a separate group with city = NULL. For GROUP BY, as for DISTINCT, all NULLs are one group.

How one group becomes one row is easier to see on a few rows. The last three orders are six rows in order_items:

SQLdatabase: shop

SELECT order_id, product_id, quantity, unit_price
FROM order_items
WHERE order_id >= 58;

SELECT order_id, product_id, quantity, unit_price FROM order_items WHERE order_id >= 58;

▶ RunRestore the queryFresh database

SELECT order_id, COUNT(*) AS lines, ROUND(SUM(quantity * unit_price), 2) AS total
FROM order_items
WHERE order_id >= 58
GROUP BY order_id;

Step by step, in words

1. 6 rows from order_items: the products in orders 58, 59 and 60.
2. GROUP BY order_id: every row goes into the group of its order_id.
3. 3 different order_id values, 3 groups. Each has 2 rows.
4. The group of 58 becomes one row: COUNT(*) is 2, and SUM(quantity * unit_price) is 1 × 1.20 + 4 × 1.90 = 8.80 €.
5. The group of 59: 2 rows, 3 × 2.50 + 2 × 3.20 = 13.90 €.
6. The group of 60: 2 rows, 2 × 1.20 + 4 × 2.90 = 14.00 €.
7. 6 rows went in, 3 came out: one for each group.

The query from the scene. ROUND(…, 2) rounds the sum to the cent:

SQLdatabase: shop

SELECT order_id, COUNT(*) AS lines, ROUND(SUM(quantity * unit_price), 2) AS total
FROM order_items
WHERE order_id >= 58
GROUP BY order_id;

SELECT order_id, COUNT(*) AS lines, ROUND(SUM(quantity * unit_price), 2) AS total FROM order_items WHERE order_id >= 58 GROUP BY order_id;

▶ RunRestore the queryFresh database

Try it

Orders per customer

How many orders has each customer placed? Show customer_id and the count.

SQLdatabase: shop

SELECT customer_id FROM orders;

SELECT customer_id FROM orders;

Check▶ Run

The idea

The value of each order

In order_items an order takes several rows, one for each product. A group by order_id brings together the lines of one order:

SQLdatabase: shop

SELECT order_id, COUNT(*) AS lines, SUM(quantity * unit_price) AS total
FROM order_items
GROUP BY order_id;

SELECT order_id, COUNT(*) AS lines, SUM(quantity * unit_price) AS total FROM order_items GROUP BY order_id;

▶ RunRestore the queryFresh database

Now each order is one row: how many products it has and how much it is worth.

A group can also be by two columns. Then one group holds the rows where both values are the same. The orders of customer 1 by status:

SQLdatabase: shop

SELECT customer_id, status, COUNT(*)
FROM orders
WHERE customer_id = 1
GROUP BY customer_id, status;

SELECT customer_id, status, COUNT(*) FROM orders WHERE customer_id = 1 GROUP BY customer_id, status;

▶ RunRestore the queryFresh database

A group can also be by a computed value. strftime('%Y-%m', ordered_at) takes the year and month from the date, and the orders are grouped by month:

SQLdatabase: shop

SELECT strftime('%Y-%m', ordered_at) AS month, COUNT(*)
FROM orders
GROUP BY strftime('%Y-%m', ordered_at);

SELECT strftime('%Y-%m', ordered_at) AS month, COUNT(*) FROM orders GROUP BY strftime('%Y-%m', ordered_at);

▶ RunRestore the queryFresh database

See it

The column that is not in the group

The orders per customer, and next to them the status. Looks reasonable:

SQLdatabase: shop

SELECT customer_id, status, COUNT(*)
FROM orders
GROUP BY customer_id;

SELECT customer_id, status, COUNT(*) FROM orders GROUP BY customer_id;

▶ RunRestore the queryFresh database

1. The query showed “8 | cancelled | 3”. What does it mean?

Customer 8 has three cancelled ordersCustomer 8 has three orders, and cancelled is the status of one of them, picked by SQLiteCustomer 8 cancelled three times

Check

The orders of customer 8 show what is going on:

SQLdatabase: shop

SELECT id, status FROM orders WHERE customer_id = 8;

SELECT id, status FROM orders WHERE customer_id = 8;

▶ RunRestore the queryFresh database

Three orders, three different statuses. The group is one row, and it has room for one value. SQLite says nothing and takes the status from one of the rows. Which one is not promised.

The rule: every column in SELECT is either in GROUP BY or inside an aggregate (COUNT, SUM, MIN…). PostgreSQL and SQL Server give an error for such a query, and so does MySQL with its default settings since version 5.7. SQLite accepts it and shows something that looks like an answer.

If you want the statuses, group by both columns: GROUP BY customer_id, status.

More precisely, if you are curious

SQLite has one quirk that is written in its documentation: if the query has exactly one MIN or MAX, the free column comes from the row with the smallest or largest value. SELECT customer_id, MAX(ordered_at), id FROM orders GROUP BY customer_id gives the number of the latest order. This is SQLite only. Other databases refuse it, and in the subqueries module we will see how to do it everywhere.

Try it

The best sellers

Which five products have sold the most items? Show product_id and the items in a column called sold, the best seller first.

SQLdatabase: shop

Check▶ Run

Exercise

Two reports

1. How many products and how many items in stock are there in each category?

Show category_id, the number of products and the sum of their stock.

SQLdatabase: shop

Check▶ Run

2. Accounting wants the number of orders for each month of 2026, sorted by month.

Show the month (year-month) and the number of orders for each month of 2026, in month order.

SQLdatabase: shop

Check▶ Run

Test

Check yourself

1. How many rows does SELECT status, COUNT(*) FROM orders GROUP BY status return?

One60, one for each orderAs many as there are different statuses

2. Boris has no city. Where is he in SELECT city, COUNT(*) FROM customers GROUP BY city?

Not in the resultIn the group of the first cityIn a separate group with city = NULL

3. What does GROUP BY customer_id, status give?

A group for each pair of customer and status that exists in the dataA group for each statusA group for each customer

4. What is safe in the SELECT of a query with GROUP BY order_id?

Only aggregatesorder_id and aggregates such as SUM(quantity)Any column of order_items

Check

Start the lesson over

Related lessons

• Builds on: ORDER BY, LIMIT and DISTINCT · Arithmetic in a query · Date functions · Aggregate functions: COUNT, SUM, AVG
• Continues in: HAVING: a condition for the groups · The order of query execution · LEFT JOIN: rows without a match too · Many tables, and a table joined to itself
• Tasks on this topic: Summarising