SQL practice › SQL reference › GROUP BY

SQL GROUP BY: one row per group, with queries you can run

GROUP BY folds the rows that share a value into one row per value, so that COUNT, SUM and AVG give one number for each group instead of one for the whole table. SELECT product, COUNT(*) FROM sales GROUP BY product returns one row per product, with its number of sales.

The rule that keeps the numbers right: every column in the SELECT is either in the GROUP BY or inside an aggregate function. Most databases refuse a query that breaks it. SQLite, which runs this page, doesn't: it fills the column from an arbitrary row of the group, and the result looks fine.

Skip to the 4 exercises ↓

Syntax

SELECT product, COUNT(*), SUM(amount)
FROM sales
WHERE sale_date >= '2026-01-01'
GROUP BY product
HAVING COUNT(*) > 1
ORDER BY SUM(amount) DESC
SELECT product, COUNT(*), SUM(amount)
The grouped column, then the aggregates. Each aggregate is computed once per group.
WHERE sale_date >= '2026-01-01'
Optional. Removes rows before the groups are made.
GROUP BY product
One group, and one result row, for each different value of product.
HAVING COUNT(*) > 1
Optional. Removes whole groups after they are counted. See WHERE vs HAVING.
ORDER BY SUM(amount) DESC
Optional. Sorts the groups. It is written last and can sort by an aggregate.

The table

Ten sales by four reps in two regions. Nine are from January to March 2026. The first row is from January 2025, and it matters when you group by month.

sales
idrepregionproductamountsale_date
1MiaWestLaptop9002025-01-20
2MiaWestLaptop9502026-01-06
3LeoEastPhone6002026-01-14
4MiaWestPhone6502026-01-22
5SamEastLaptop9002026-02-03
6LeoEastPhone5502026-02-10
7IvyWestLaptop10002026-02-19
8SamEastTablet4002026-03-02
9LeoEastPhone6002026-03-11
10IvyWestTablet4502026-03-18
Live queryOne row per product: COUNT, SUM and AVG
Table: sales
sales
idrepregionproductamountsale_date
1MiaWestLaptop9002025-01-20
2MiaWestLaptop9502026-01-06
3LeoEastPhone6002026-01-14
4MiaWestPhone6502026-01-22
5SamEastLaptop9002026-02-03
6LeoEastPhone5502026-02-10
7IvyWestLaptop10002026-02-19
8SamEastTablet4002026-03-02
9LeoEastPhone6002026-03-11
10IvyWestTablet4502026-03-18
Ctrl/⌘ + Enter to run
Result · 3 rows
productsalestotalaverage
Laptop43750937.5
Phone42400600
Tablet2850425

How the database reads it

  1. It takes the rows of sales (the ones that pass the WHERE, if there is one).
  2. It puts the rows with the same product in the same pile: one pile for Laptop, one for Phone, one for Tablet.
  3. It returns one row per pile. COUNT(*) counts the pile's rows, SUM(amount) adds its amounts, AVG(amount) averages them.

That is why a plain column in the SELECT has to be one you grouped by. The Laptop pile has one product, but several reps, amounts and dates: there is no single value to show for those.

GROUP BY multiple columns

List two columns and a group is each combination of values that exists in the data. The order of the columns in GROUP BY doesn't change the groups.

Live queryTwo columns: region and product
Table: sales
sales
idrepregionproductamountsale_date
1MiaWestLaptop9002025-01-20
2MiaWestLaptop9502026-01-06
3LeoEastPhone6002026-01-14
4MiaWestPhone6502026-01-22
5SamEastLaptop9002026-02-03
6LeoEastPhone5502026-02-10
7IvyWestLaptop10002026-02-19
8SamEastTablet4002026-03-02
9LeoEastPhone6002026-03-11
10IvyWestTablet4502026-03-18
Ctrl/⌘ + Enter to run
Result · 6 rows
regionproductsalestotal
EastLaptop1900
EastPhone31750
EastTablet1400
WestLaptop32850
WestPhone1650
WestTablet1450

GROUP BY month

A date column has too many different values to group by as it is: in this table every sale would be its own group. Turn the date into its month first and group by that. In SQLite, strftime('%Y-%m', sale_date) turns 2026-03-18 into 2026-03. PostgreSQL has date_trunc('month', sale_date) for the same job.

Live querySales per month
Table: sales
sales
idrepregionproductamountsale_date
1MiaWestLaptop9002025-01-20
2MiaWestLaptop9502026-01-06
3LeoEastPhone6002026-01-14
4MiaWestPhone6502026-01-22
5SamEastLaptop9002026-02-03
6LeoEastPhone5502026-02-10
7IvyWestLaptop10002026-02-19
8SamEastTablet4002026-03-02
9LeoEastPhone6002026-03-11
10IvyWestTablet4502026-03-18
Ctrl/⌘ + Enter to run
Result · 4 rows
monthsalestotal
2025-011900
2026-0132200
2026-0232450
2026-0331450

GROUP BY and ORDER BY

GROUP BY decides which rows come back, not their order: SQLite's documentation says the order of the rows is undefined when there is no ORDER BY. To sort the groups, write ORDER BY after GROUP BY. It can sort by an aggregate, which is how you get "best first".

Live queryReps by total, best first
Table: sales
sales
idrepregionproductamountsale_date
1MiaWestLaptop9002025-01-20
2MiaWestLaptop9502026-01-06
3LeoEastPhone6002026-01-14
4MiaWestPhone6502026-01-22
5SamEastLaptop9002026-02-03
6LeoEastPhone5502026-02-10
7IvyWestLaptop10002026-02-19
8SamEastTablet4002026-03-02
9LeoEastPhone6002026-03-11
10IvyWestTablet4502026-03-18
Ctrl/⌘ + Enter to run
Result · 4 rows
reptotal
Mia2500
Leo1750
Ivy1450
Sam1300

Three ways to get wrong numbers without an error

1. Selecting a column you didn't group by. You group by region and also select the rep. A region has several reps, so which one should the row show? PostgreSQL's documentation says that columns not listed in GROUP BY cannot be referenced except in aggregate expressions, and MySQL's default mode rejects such a query. SQLite runs it. Its documentation calls rep a "bare" column here and says its value comes from an arbitrarily chosen row of the group.

Live queryA bare column: one rep's name, the whole region's total
Table: sales
sales
idrepregionproductamountsale_date
1MiaWestLaptop9002025-01-20
2MiaWestLaptop9502026-01-06
3LeoEastPhone6002026-01-14
4MiaWestPhone6502026-01-22
5SamEastLaptop9002026-02-03
6LeoEastPhone5502026-02-10
7IvyWestLaptop10002026-02-19
8SamEastTablet4002026-03-02
9LeoEastPhone6002026-03-11
10IvyWestTablet4502026-03-18
Ctrl/⌘ + Enter to run
Result · 2 rows
regionreptotal
EastLeo3050
WestMia3950

SQLite documents one exception: when the query has exactly one MIN() or MAX(), the bare columns come from the row that holds that minimum or maximum. The same page says most other database engines raise an error for a bare column, so don't lean on it in a query that has to run elsewhere.

2. Grouping by month without the year. strftime('%m', sale_date) gives 01 for every January there has ever been. The month query above shows it: with '%m', January 2025 and January 2026 become one group.

3. Grouping by a label that isn't unique. Group customers by name and two people called Sam are counted as one. When the table has an id, group by the id, or by the id and the name together.

Practice: 4 exercises

Write the query in the box and press Run. Your result is compared with the right one, on this data and again on changed data, so a query with the values typed in won't pass. Column names don't matter; the columns and rows do.

Exercise 1Sales per rep

Return each rep and how many sales they made.

Table: sales
sales
idrepregionproductamountsale_date
1MiaWestLaptop9002025-01-20
2MiaWestLaptop9502026-01-06
3LeoEastPhone6002026-01-14
4MiaWestPhone6502026-01-22
5SamEastLaptop9002026-02-03
6LeoEastPhone5502026-02-10
7IvyWestLaptop10002026-02-19
8SamEastTablet4002026-03-02
9LeoEastPhone6002026-03-11
10IvyWestTablet4502026-03-18
Ctrl/⌘ + Enter to run
Show the answer
SELECT rep, COUNT(*)
FROM sales
GROUP BY rep
Result · 4 rows
repCOUNT(*)
Ivy2
Leo3
Mia3
Sam2

GROUP BY rep makes one group per rep and COUNT(*) counts the rows in each. Without the GROUP BY there is a single count for the whole table.

Exercise 2Total and average per product

Return each product, the total amount sold and the average amount of a sale.

Table: sales
sales
idrepregionproductamountsale_date
1MiaWestLaptop9002025-01-20
2MiaWestLaptop9502026-01-06
3LeoEastPhone6002026-01-14
4MiaWestPhone6502026-01-22
5SamEastLaptop9002026-02-03
6LeoEastPhone5502026-02-10
7IvyWestLaptop10002026-02-19
8SamEastTablet4002026-03-02
9LeoEastPhone6002026-03-11
10IvyWestTablet4502026-03-18
Ctrl/⌘ + Enter to run
Show the answer
SELECT product, SUM(amount), AVG(amount)
FROM sales
GROUP BY product
Result · 3 rows
productSUM(amount)AVG(amount)
Laptop3750937.5
Phone2400600
Tablet850425

Several aggregates can sit in the same SELECT: each one is computed over the rows of the group. AVG(amount) is the group's total divided by its number of sales.

Exercise 3Two columns: rep and product

For each combination of rep and product that has sales, return the rep, the product and the number of sales.

Table: sales
sales
idrepregionproductamountsale_date
1MiaWestLaptop9002025-01-20
2MiaWestLaptop9502026-01-06
3LeoEastPhone6002026-01-14
4MiaWestPhone6502026-01-22
5SamEastLaptop9002026-02-03
6LeoEastPhone5502026-02-10
7IvyWestLaptop10002026-02-19
8SamEastTablet4002026-03-02
9LeoEastPhone6002026-03-11
10IvyWestTablet4502026-03-18
Ctrl/⌘ + Enter to run
Show the answer
SELECT rep, product, COUNT(*)
FROM sales
GROUP BY rep, product
Result · 7 rows
repproductCOUNT(*)
IvyLaptop1
IvyTablet1
LeoPhone3
MiaLaptop2
MiaPhone1
SamLaptop1
SamTablet1

Grouping by rep, product gives one row per pair that occurs. Group by rep alone and SQLite still runs the query, but returns four rows with one arbitrary product each and a count that covers all of that rep's sales.

Exercise 4Total per month, latest first

For each month, return the month as text like 2026-03 and the total amount sold in it, the most recent month first.

Table: sales
sales
idrepregionproductamountsale_date
1MiaWestLaptop9002025-01-20
2MiaWestLaptop9502026-01-06
3LeoEastPhone6002026-01-14
4MiaWestPhone6502026-01-22
5SamEastLaptop9002026-02-03
6LeoEastPhone5502026-02-10
7IvyWestLaptop10002026-02-19
8SamEastTablet4002026-03-02
9LeoEastPhone6002026-03-11
10IvyWestTablet4502026-03-18
Ctrl/⌘ + Enter to run
Show the answer
SELECT strftime('%Y-%m', sale_date) AS month, SUM(amount)
FROM sales
GROUP BY month
ORDER BY month DESC
Result · 4 rows
monthSUM(amount)
2026-031450
2026-022450
2026-012200
2025-01900

The month text 2026-03 sorts correctly as text because the year comes first, so ORDER BY month DESC puts the latest month on top. SQLite accepts the alias month in GROUP BY and ORDER BY; if your database doesn't, repeat the strftime expression there.

Questions people ask

What is the difference between GROUP BY and ORDER BY?

GROUP BY changes how many rows you get: it folds the rows with the same value into one. ORDER BY never changes the number of rows, it only sorts them. They are often used together: GROUP BY first, then ORDER BY to sort the groups, for example by SUM(amount) DESC.

What is the difference between GROUP BY and DISTINCT?

With no aggregate in the query they return the same rows: SELECT DISTINCT region, product FROM sales and SELECT region, product FROM sales GROUP BY region, product both give the six combinations in this table. Use DISTINCT when you only want the different values, and GROUP BY when you want a number for each one: DISTINCT can't count or add per group.

How do I GROUP BY month in SQL?

Group by an expression that turns the date into its month, keeping the year. In SQLite that is strftime('%Y-%m', sale_date); PostgreSQL has date_trunc('month', sale_date). Put the same expression in the SELECT so each row says which month it is.

Why does my database say a column must appear in the GROUP BY clause?

Because the SELECT has a plain column that isn't grouped and isn't inside an aggregate, so there is no single value to show for the group. Add the column to the GROUP BY, or wrap it in an aggregate such as MAX(), or remove it. SQLite doesn't raise this error, which is worse: see the bare-column query above. More on the GROUP BY error.

What happens to NULL in GROUP BY?

The rows where the grouped column is NULL form one group of their own: for grouping, SQLite's documentation says NULL values are considered equal. More on NULL.

Keep going

Lesson 2 is seven drills on an orders table: COUNT, SUM and AVG, GROUP BY, HAVING and COUNT(DISTINCT). It's free, like lessons 1, 3 and 4 and the level test.

Related

SQL WHERE vs HAVING, with live queriesSQL COUNT(DISTINCT), with live queriesColumn must appear in the GROUP BY clause (not a GROUP BY expression)SQL GROUP BY practiceSQL skills testSQL reference with queries you can runSQL practice online

Written and tested by Nico, who builds SheetDrill (about). Every query on this page was run on SQLite, and every exercise checked against right and wrong answers, before the page was published. Other databases differ in places; where they do, the page says so. Checked against: SQLite documentation: SELECT (GROUP BY, bare columns, ORDER BY), SQLite documentation: Date and Time Functions, PostgreSQL documentation: Table Expressions (GROUP BY and HAVING), PostgreSQL documentation: Date/Time Functions (date_trunc), MySQL 8.4 Reference Manual: MySQL Handling of GROUP BY. Last checked November 2026.