SQL GROUP BY

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which query gives the average score for each class?

A SELECT class, AVG(score) FROM results;

B SELECT class, AVG(score) FROM results GROUP BY class;

2. A table has 90 student rows from 3 classes. How many rows does GROUP BY class return?

A 3 rows, one for each class

B 90 rows, one for each student

3. What does GROUP BY do?

A Deletes rows that have the same field value

B Puts rows with the same field value into groups so an aggregate can be applied to each group

4. Which query counts how many students are in each year level?

A SELECT year, COUNT(*) FROM students GROUP BY year;

B SELECT year, COUNT(*) FROM students GROUP BY COUNT(*);

5. Which query shows the groups with the highest average score first?

A SELECT class, AVG(score) FROM results GROUP BY class ORDER BY AVG(score) DESC;

B SELECT class, AVG(score) FROM results ORDER BY AVG(score) DESC GROUP BY class;

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1SELECT class, AVG(score) FROM results GROUP BY class;		ACounts the students in each year level and lists the biggest group first
2SELECT year, COUNT() FROM students GROUP BY year ORDER BY COUNT() DESC;		BGives the average score for each class, with one result row per class
3Splitting rows into groups that share a value, then adding up a number for each group, such as total sales per shop		CSELECT team, AVG(points) FROM games GROUP BY team ORDER BY AVG(points) DESC;
4Sorting the grouped results so the group with the highest average comes first		DThe clauses are in the wrong order. GROUP BY must come before ORDER BY.
5SELECT class, AVG(score) FROM results;		EGROUP BY class is missing, so AVG(score) is not worked out for each class. Add GROUP BY class before the semicolon.
6SELECT class, COUNT(*) FROM students ORDER BY class GROUP BY class;		FSELECT shop, SUM(sales) FROM orders GROUP BY shop;

Definitions

GROUP BY

Fill in the Blanks

Complete each line of code by filling in the missing parts.

SELECT class, AVG(score) FROM results ________ class;

SELECT year, _____(*) FROM students GROUP BY year;

SELECT team, SUM(points) FROM games GROUP BY ____;

SELECT class, AVG(score) FROM results GROUP BY class ________ AVG(score) DESC;

SELECT pet, COUNT(*) FROM survey GROUP BY pet ORDER BY COUNT(*) ____;

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A SELECT class, AVG(score)

B FROM results

C GROUP class;

Question 2

A SELECT team, COUNT(*)

B FROM players

C GROUP BY players;

Question 3

A SELECT house, SUM(house)

B FROM scores

C GROUP BY house;

Question 4

A SELECT class, AVG(score)

B FORM results

C GROUP BY class

D ORDER BY AVG(score) DESC;

Question 5

A SELECT city, MAX temp

B FROM weather

C GROUP BY city;

Answer Key

SQL GROUP BY

Section A: Quick Questions

B — SELECT class, AVG(score) FROM results GROUP BY class;

A — 3 rows, one for each class

B — Puts rows with the same field value into groups so an aggregate can be applied to each group

A — SELECT year, COUNT(*) FROM students GROUP BY year;

A — SELECT class, AVG(score) FROM results GROUP BY class ORDER BY AVG(score) DESC;

Section B: Match the Pairs

SELECT class, AVG(score) FROM results GROUP BY class; → Gives the average score for each class, with one result row per class

SELECT year, COUNT(*) FROM students GROUP BY year ORDER BY COUNT(*) DESC; → Counts the students in each year level and lists the biggest group first

Splitting rows into groups that share a value, then adding up a number for each group, such as total sales per shop → SELECT shop, SUM(sales) FROM orders GROUP BY shop;

Sorting the grouped results so the group with the highest average comes first → SELECT team, AVG(points) FROM games GROUP BY team ORDER BY AVG(points) DESC;

SELECT class, AVG(score) FROM results; → GROUP BY class is missing, so AVG(score) is not worked out for each class. Add GROUP BY class before the semicolon.

SELECT class, COUNT(*) FROM students ORDER BY class GROUP BY class; → The clauses are in the wrong order. GROUP BY must come before ORDER BY.

Section C: Definitions

GROUP BY — An SQL clause that sorts rows into groups sharing the same field value, then applies an aggregate like AVG or COUNT to each group, giving one result row per group (e.g. average score per class).

Section D: Fill in the Blanks

SELECT class, AVG(score)
FROM results
GROUP BY class;

SELECT year, COUNT(*)
FROM students
GROUP BY year;

SELECT team, SUM(points)
FROM games
GROUP BY team;

SELECT class, AVG(score)
FROM results
GROUP BY class ORDER BY AVG(score) DESC;

SELECT pet, COUNT(*)
FROM survey GROUP BY pet
ORDER BY COUNT(*) DESC;

Section E: Spot the Error

C — The clause is called GROUP BY. The word BY is missing. It should be GROUP BY class;

C — GROUP BY needs a column to make groups from, not the table name. To count players in each team it should be GROUP BY team;

A — The aggregate must work on the number column that is being totalled. House is text, so it cannot be added up. It should be SUM(points).

B — FROM is spelled wrong. It should be FROM results so SQL knows which table to read.

A — An aggregate function needs brackets around its column. It should be MAX(temp).