← VCE Spreadsheets, SQL & Visualisation

SQL 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).

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 55, VCE Data Analytics - U3O2 - Analysis and Design — Module 41, 9-10 Digital Technologies: Analysing and visualising data — Module 13, QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 68, QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 35, QCE Digital Solutions Unit 3 — Innovative Digital Solutions — Module 32, QCE Digital Solutions Unit 4 — Prototype Digital Data Exchanges — Module 69, WA Computer Science ATAR Year 11 — Data Management — Module 73

Understanding SQL GROUP BY in VCE Spreadsheets, SQL & Visualisation

What it does: GROUP BY shop makes one group per shop and SUM(sales) adds up the sales inside each group. ORDER BY comes after GROUP BY and sorts the group results. DESC puts the highest average at the top. GROUP BY class splits the rows into one group per class, so AVG works out the average for each group. Without it, SQL cannot pair the class column with a single average. The column you group by (year) goes after GROUP BY. The aggregate function COUNT(*) belongs in the SELECT list, not after GROUP BY.

Common mistakes: The clause is called GROUP BY. The word BY is missing. It should be GROUP BY class;. 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;.

Class: _____________________ · Name: _____________________ · Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add SQL GROUP BY to a class →

This module's key terms are also in a printable crossword (Modules 55–70) and word search (Modules 55–86).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning

Duo

Which query gives the average score for each class?

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

Matches

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.

Applying

Meanings

Term

GROUP BY

Meaning

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).

Practice

SELECT class, AVG(score) AS avg_score

Testing

Gaps

class;

Check Answer

Spots

2FROM results

3GROUP class;

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy

Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games

Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print

Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

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; ✓

GROUP BY class splits the rows into one group per class, so AVG works out the average for each group. Without it, SQL cannot pair the class column with a single average.

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

GROUP BY gives one result row per group. There are 3 different class values, so there are 3 result rows.

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 ✓

GROUP BY does not remove data. It sorts rows into groups that share a value, and then COUNT, SUM, AVG, MIN or MAX works on 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(*);

The column you group by (year) goes after GROUP BY. The aggregate function COUNT(*) belongs in the SELECT list, not after GROUP BY.

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;

ORDER BY comes after GROUP BY. SQL first makes the groups, and then it sorts the grouped results. DESC puts the highest value first.

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.

Key Term

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).

Fill in the Blanks

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

Hint: Type the two-word clause that splits rows into groups.

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

Hint: Type the aggregate function that tallies the rows in each group.

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

Hint: Type the field name whose matching values form each group.

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

Hint: Type the clause that sorts the grouped results.

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

Hint: Type the keyword that puts the largest group totals first.

Spot the Error

Question 1

A SELECT class, AVG(score)

B FROM results

C GROUP class;

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

Question 2

A SELECT team, COUNT(*)

B FROM players

C GROUP BY players;

Line 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;

Question 3

A SELECT house, SUM(house)

B FROM scores

C GROUP BY house;

Line 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).

Question 4

A SELECT class, AVG(score)

B FORM results

C GROUP BY class

D ORDER BY AVG(score) DESC;

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

Question 5

A SELECT city, MAX temp

B FROM weather

C GROUP BY city;

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

Student with a class code? Join your class →