SQL exercises with solutions: GROUP BY

Updated on 22 September 2026

GROUP BY computes one summary per group: one result row per value of the column. These 15 exercises range from level 2 to level 3; they use the syntax of SQLite, SpeedQL’s SQL engine.

Tip: Every column shown without an aggregate must appear in the GROUP BY.

Read the “GROUP BY” card in the cheat sheet

Exercise 1 · level 2

For each department, show the department and its number of employees.

Table `employees` (8 rows)
id	name	department	salary
1	Alice	Finance	32000
2	Bob	IT	41000
3	Claire	Finance	38000
4	David	HR	29000
5	Emma	IT	50000
6	Farid	IT	47000
7	Gaelle	HR	33000
8	Hugo	Finance	44000

Show the hint

Topics to use: COUNT, SUM, AVG, MIN, MAX, GROUP BY.

Query structure:

SELECT …, COUNT(*)
FROM …
GROUP BY …

Show the solution

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Expected result (3 rows):

department	COUNT(*)
Finance	3
HR	2
IT	3

Exercise 2 · level 2

For each department, show the department and the average salary of its employees.

Table `employees` (8 rows)
id	name	department	salary
1	Alice	Finance	32000
2	Bob	IT	41000
3	Claire	Finance	38000
4	David	HR	29000
5	Emma	IT	50000
6	Farid	IT	47000
7	Gaelle	HR	33000
8	Hugo	Finance	44000

Show the hint

Topics to use: COUNT, SUM, AVG, MIN, MAX, GROUP BY.

Query structure:

SELECT …, AVG(…)
FROM …
GROUP BY …

Show the solution

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

Expected result (3 rows):

department	AVG(salary)
Finance	38000
HR	31000
IT	46000

Exercise 3 · level 2

For each department, show the department and the total of the salaries paid.

Table `employees` (8 rows)
id	name	department	salary
1	Alice	Finance	32000
2	Bob	IT	41000
3	Claire	Finance	38000
4	David	HR	29000
5	Emma	IT	50000
6	Farid	IT	47000
7	Gaelle	HR	33000
8	Hugo	Finance	44000

Show the hint

Topics to use: COUNT, SUM, AVG, MIN, MAX, GROUP BY.

Query structure:

SELECT …, SUM(…)
FROM …
GROUP BY …

Show the solution

SELECT department, SUM(salary)
FROM employees
GROUP BY department;

Expected result (3 rows):

department	SUM(salary)
Finance	114000
HR	62000
IT	138000

Exercise 4 · level 2

For each category, show the category and the total stock of its products.

Table `products` (6 rows)
id	name	category	price	stock
1	Keyboard	Office	25	40
2	Mouse	Office	15	0
3	Screen	Display	180	12
4	Headset	Audio	60	8
5	Webcam	Office	45	0
6	Speaker	Audio	35	25

Show the hint

Topics to use: COUNT, SUM, AVG, MIN, MAX, GROUP BY.

Query structure:

SELECT …, SUM(…)
FROM …
GROUP BY …

Show the solution

SELECT category, SUM(stock)
FROM products
GROUP BY category;

Expected result (3 rows):

category	SUM(stock)
Audio	33
Display	12
Office	40

Exercise 5 · level 2

For each genre, show the genre and the number of movies.

Table `movies` (10 rows)
id	title	genre	year	duration	rating	director_id
1	Night Train	Thriller	2015	118	7.8	1
2	Blue Harbor	Drama	2018	102	7.1	2
3	Paper Moon City	Comedy	2012	95	6.4	5
4	Silent Peak	Drama	2020	131	8.2	3
5	Last Signal	Sci-Fi	2019	142	7.5	1
6	Summer Keys	Comedy	2016	88	5.9	4
7	Iron Garden	Sci-Fi	2021	125	8	3
8	Dust and Gold	Western	2014	110	6.8	2
9	The Quiet Hour	Drama	2022	97	7.4	5
10	Deep Current	Thriller	2017	105	6.9	NULL

Show the hint

Topics to use: COUNT, SUM, AVG, MIN, MAX, GROUP BY.

Query structure:

SELECT …, COUNT(*)
FROM …
GROUP BY …

Show the solution

SELECT genre, COUNT(*)
FROM movies
GROUP BY genre;

Expected result (5 rows):

genre	COUNT(*)
Comedy	2
Drama	3
Sci-Fi	2
Thriller	2
Western	1

Exercise 6 · level 2

For each city, show the city and the number of members.

Table `members` (6 rows)
id	name	city	joined
1	Lena	Lyon	2022-01-15
2	Marc	Paris	2021-06-03
3	Nadia	Lyon	2023-03-20
4	Oscar	Lille	2020-11-11
5	Paula	Paris	2024-02-01
6	Quentin	Nantes	2023-09-09

Show the hint

Topics to use: COUNT, SUM, AVG, MIN, MAX, GROUP BY.

Query structure:

SELECT …, COUNT(*)
FROM …
GROUP BY …

Show the solution

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

Expected result (4 rows):

city	COUNT(*)
Lille	1
Lyon	2
Nantes	1
Paris	2

Exercise 7 · level 2

For each airline, show the airline, the number of flights and the average price.

Table `flights` (12 rows)
id	airline	origin	dest	departs	duration_min	price	seats_sold	capacity
1	SkyJet	CDG	MAD	2025-06-01 08:10	125	89	150	180
2	AirNova	CDG	LIS	2025-06-01 11:40	155	120	160	170
3	BlueWing	LYS	FCO	2025-06-02 07:30	95	75	110	150
4	SkyJet	MAD	CDG	2025-06-02 18:20	120	95	170	180
5	AirNova	BER	CDG	2025-06-03 09:05	110	105	140	160
6	BlueWing	NCE	BER	2025-06-03 13:50	130	140	90	150
7	SkyJet	CDG	FCO	2025-06-04 06:45	135	99	175	180
8	AirNova	LIS	MAD	2025-06-04 16:15	75	65	60	120
9	BlueWing	FCO	LYS	2025-06-05 20:30	100	82	130	150
10	SkyJet	CDG	BER	2025-06-05 10:00	105	110	120	180
11	AirNova	MAD	LIS	2025-06-06 12:25	80	70	95	120
12	BlueWing	LYS	MAD	2025-06-06 15:40	115	79	100	150

Show the hint

Topics to use: COUNT, SUM, AVG, MIN, MAX, GROUP BY.

Query structure:

SELECT …, COUNT(*), AVG(…)
FROM …
GROUP BY …

Show the solution

SELECT airline, COUNT(*), AVG(price)
FROM flights
GROUP BY airline;

Expected result (3 rows):

airline	COUNT(*)	AVG(price)
AirNova	4	90
BlueWing	4	94
SkyJet	4	98.25

Exercise 8 · level 2

For each genre, show the genre, the number of songs and the average duration in seconds.

Table `songs` (10 rows)
id	title	artist_id	genre	duration_s	released
1	Glass Heart	1	Pop	214	2019-04-12
2	Low Tide	1	Pop	198	2021-06-01
3	Rust	2	Rock	256	2008-09-30
4	Wires	2	Rock	301	2015-02-14
5	Sunday Market	3	Afrobeat	233	2020-11-20
6	Palm Wine	3	Afrobeat	245	2022-03-03
7	Alma	4	Latin	189	2013-07-07
8	Brisa	4	Pop	205	2018-05-25
9	Night Drive	5	Electro	276	2021-10-10
10	Pulse	5	Electro	230	2023-01-15

Show the hint

Topics to use: COUNT, SUM, AVG, MIN, MAX, GROUP BY.

Query structure:

SELECT …, COUNT(*), AVG(…)
FROM …
GROUP BY …

Show the solution

SELECT genre, COUNT(*), AVG(duration_s)
FROM songs
GROUP BY genre;

Expected result (5 rows):

genre	COUNT(*)	AVG(duration_s)
Afrobeat	2	239
Electro	2	253
Latin	1	189
Pop	3	205.66666666666666
Rock	2	278.5

Exercise 9 · level 2

For each city, show the city, the highest maximum temperature recorded and the lowest minimum temperature recorded.

Table `readings` (15 rows)
id	city	day	temp_max	temp_min	rain_mm
1	Paris	2025-07-01	24	15	0
2	Paris	2025-07-02	27	17	0
3	Paris	2025-07-03	22	16	4.5
4	Paris	2025-07-04	19	14	12
5	Paris	2025-07-05	23	13	0
6	Lyon	2025-07-01	26	16	0
7	Lyon	2025-07-02	29	18	0
8	Lyon	2025-07-03	31	19	0
9	Lyon	2025-07-04	24	17	8.5
10	Lyon	2025-07-05	22	15	3
11	Marseille	2025-07-01	30	21	0
12	Marseille	2025-07-02	32	22	0
13	Marseille	2025-07-03	33	23	0
14	Marseille	2025-07-04	29	21	0
15	Marseille	2025-07-05	28	20	0

Show the hint

Topics to use: COUNT, SUM, AVG, MIN, MAX, GROUP BY.

Query structure:

SELECT …, MAX(…), MIN(…)
FROM …
GROUP BY …

Show the solution

SELECT city, MAX(temp_max), MIN(temp_min)
FROM readings
GROUP BY city;

Expected result (3 rows):

city	MAX(temp_max)	MIN(temp_min)
Lyon	31	15
Marseille	33	20
Paris	27	13

Exercise 10 · level 2

For each room type, show the type and the average price rounded to 1 decimal.

Table `rooms` (10 rows)
id	hotel_id	type	price
1	1	single	70
2	1	double	95
3	2	double	130
4	2	suite	210
5	3	single	110
6	3	double	150
7	4	double	65
8	5	double	260
9	5	suite	480
10	5	single	190

Show the hint

Topics to use: COUNT, SUM, AVG, MIN, MAX, GROUP BY.

Query structure:

SELECT …, ROUND(AVG(…), …)
FROM …
GROUP BY …

Show the solution

SELECT type, ROUND(AVG(price), 1)
FROM rooms
GROUP BY type;

Expected result (3 rows):

type	ROUND(AVG(price), 1)
double	140
single	123.3
suite	345

Exercise 11 · level 2

For each order status, show the status and the number of orders.

Table `orders` (8 rows)
id	customer_id	order_date	status
1	1	2025-01-05	shipped
2	2	2025-01-12	shipped
3	1	2025-02-03	paid
4	3	2025-02-10	cancelled
5	4	2025-02-20	shipped
6	5	2025-03-02	shipped
7	2	2025-03-15	paid
8	3	2025-03-28	shipped

Show the hint

Topics to use: COUNT, SUM, AVG, MIN, MAX, GROUP BY.

Query structure:

SELECT …, COUNT(*)
FROM …
GROUP BY …

Show the solution

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

Expected result (3 rows):

status	COUNT(*)
cancelled	1
paid	2
shipped	5

Exercise 12 · level 2

For each status, show the status and the total number of hours of the tasks.

Table `tasks` (10 rows)
id	project_id	dev_id	title	hours	status	done_on
1	1	1	Landing page	12	done	2025-03-10
2	1	3	Data model	20	done	2025-03-20
3	1	2	Login	8	doing	NULL
4	2	4	ETL job	16	done	2025-04-02
5	2	5	CI pipeline	6	done	2025-03-15
6	3	1	Dashboard	14	todo	NULL
7	3	3	Report	10	done	2025-04-25
8	4	2	API	18	doing	NULL
9	4	5	Monitoring	9	todo	NULL
10	4	4	Forecast	22	done	2025-05-05

Show the hint

Topics to use: COUNT, SUM, AVG, MIN, MAX, GROUP BY.

Query structure:

SELECT …, SUM(…)
FROM …
GROUP BY …

Show the solution

SELECT status, SUM(hours)
FROM tasks
GROUP BY status;

Expected result (3 rows):

status	SUM(hours)
doing	26
done	86
todo	23

Exercise 13 · level 2

For each account with transactions, show the account id (account_id) and its balance (sum of the amounts).

Table `transactions` (14 rows)
id	account_id	made_on	amount	label
1	1	2025-01-02	2500	salary
2	1	2025-01-05	-60	groceries
3	1	2025-01-12	-800	rent
4	2	2025-01-15	500	transfer
5	3	2025-01-03	1900	salary
6	3	2025-01-20	-120	groceries
7	3	2025-02-01	-950	rent
8	4	2025-01-25	2100	salary
9	4	2025-02-03	-45	restaurant
10	1	2025-02-02	2600	salary
11	1	2025-02-06	-75	groceries
12	5	2025-02-10	30	interest
13	2	2025-02-15	500	transfer
14	4	2025-02-18	-600	rent

Show the hint

Topics to use: COUNT, SUM, AVG, MIN, MAX, GROUP BY.

Query structure:

SELECT …, SUM(…)
FROM …
GROUP BY …

Show the solution

SELECT account_id, SUM(amount)
FROM transactions
GROUP BY account_id;

Expected result (5 rows):

account_id	SUM(amount)
1	4165
2	1000
3	830
4	1455
5	30

Exercise 14 · level 3

For each department, show the department, the number of employees and the average salary, from the highest to the lowest average salary.

Table `employees` (8 rows)
id	name	department	salary
1	Alice	Finance	32000
2	Bob	IT	41000
3	Claire	Finance	38000
4	David	HR	29000
5	Emma	IT	50000
6	Farid	IT	47000
7	Gaelle	HR	33000
8	Hugo	Finance	44000

Show the hint

Topics to use: ORDER BY, LIMIT, COUNT, SUM, AVG, MIN, MAX, GROUP BY.

Query structure:

SELECT …, COUNT(*), AVG(…)
FROM …
GROUP BY …
ORDER BY AVG(…) DESC

Show the solution

SELECT department, COUNT(*), AVG(salary)
FROM employees
GROUP BY department
ORDER BY AVG(salary) DESC;

Expected result (3 rows, in this order):

department	COUNT(*)	AVG(salary)
IT	3	46000
Finance	3	38000
HR	2	31000

Exercise 15 · level 3

Show the department with the highest average salary.

Table `employees` (8 rows)
id	name	department	salary
1	Alice	Finance	32000
2	Bob	IT	41000
3	Claire	Finance	38000
4	David	HR	29000
5	Emma	IT	50000
6	Farid	IT	47000
7	Gaelle	HR	33000
8	Hugo	Finance	44000

Show the hint

Topics to use: ORDER BY, LIMIT, COUNT, SUM, AVG, MIN, MAX, GROUP BY.

Query structure:

SELECT …
FROM …
GROUP BY …
ORDER BY AVG(…) DESC
LIMIT …

Show the solution

SELECT department
FROM employees
GROUP BY department
ORDER BY AVG(salary) DESC
LIMIT 1;

Expected result (1 row):

department
IT

department

Practice with automatic checking

In SpeedQL, you write your query and it is checked straight away, on these tables and then on a hidden dataset.

Practice on the 27 “GROUP BY” questions