← VCE Spreadsheets, SQL & Visualisation

SQL aggregate functions: COUNT, SUM, AVG, MIN, MAX

An aggregate function works out one value from many rows. COUNT, SUM, AVG, MIN and MAX give the count, total, mean, lowest and highest. This query returns the mean score.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 54, VCE Data Analytics - U3O2 - Analysis and Design — Module 40, 9-10 Digital Technologies: Analysing and visualising data — Module 12, ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 35, QCE Digital Solutions Unit 2 — Data and Programming Techniques — Module 67, QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 33, QCE Digital Solutions Unit 3 — Innovative Digital Solutions — Module 31, QCE Digital Solutions Unit 4 — Prototype Digital Data Exchanges — Module 68, WA Computer Science ATAR Year 11 — Data Management — Module 71

Understanding SQL aggregate functions: COUNT, SUM, AVG, MIN, MAX in VCE Spreadsheets, SQL & Visualisation

What it does: SUM adds every value in the price column and returns one total. WHERE filters the rows first, then AVG works out the mean of only the Year 9 scores. COUNT(*) counts rows. SUM adds up the values in a number column, so it can't be used with * to count rows. MAX finds the highest value and MIN finds the lowest value. MIN is the aggregate function for the lowest value. LOW is not an SQL aggregate function. AVG adds the scores (18) and divides by how many there are (3), giving 6. The value 18 is what SUM would return.

Common mistakes: Text values in SQL must be in quotes. Maths should be written as 'Maths', otherwise SQL thinks it is a column name. SQL uses a single = to compare values, not ==. It should be WHERE status = 'paid';.

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 aggregate functions: COUNT, SUM, AVG, MIN, MAX to a class →

This module's key terms are also in a printable crossword (Modules 45–54) and word search (Modules 35–54).

Take this worksheet further — free with Cloodit

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

Learning

Duo

Which query counts every row in a table called students?

SELECT COUNT(*) FROM students;SELECT SUM(*) FROM students;

Matches

SELECT AVG score FROM results;

↓

The column name is missing brackets. An aggregate function needs brackets around the column, like AVG(score).

Applying

Meanings

Term

SELECT AVG(score) FROM results;

Meaning

An aggregate function works out one value from many rows. COUNT, SUM, AVG, MIN and MAX give the count, total, mean, lowest and highest. This query returns the mean score.

Practice

SELECT COUNT(*), AVG(score), MAX(score)

Testing

Gaps

SELECT (*) FROM results;

Check Answer

Spots

2FROM results

3WHERE subject = Maths;

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 counts every row in a table called students?

A. SELECT COUNT(*) FROM students; ✓

B. SELECT SUM(*) FROM students;

COUNT(*) counts rows. SUM adds up the values in a number column, so it can't be used with * to count rows.

2. Which query finds the total of all values in the column points?

A. SELECT SUM(points) FROM games; ✓

B. SELECT COUNT(points) FROM games;

SUM adds the values together to give a total. COUNT only tells you how many values there are.

3. True or false: SELECT AVG(score) FROM results; returns one value, the mean score.

A. True ✓

B. False

An aggregate function turns many rows into a single value. AVG returns the mean of all the scores.

4. Which function gives you the highest value in a column?

A. MIN(score)

B. MAX(score) ✓

MAX finds the highest value and MIN finds the lowest value.

5. Which query finds the lowest price in a table called shop?

A. SELECT LOW(price) FROM shop;

B. SELECT MIN(price) FROM shop; ✓

MIN is the aggregate function for the lowest value. LOW is not an SQL aggregate function.

Match the Pairs

SELECT COUNT(*) FROM results; → Counts how many rows are in the results table and returns that single number.

SELECT MAX(score) FROM results; → Returns the highest score in the results table.

Add up all the values in a column to get a total → SELECT SUM(price) FROM sales;

Use WHERE to work out an average for only some rows → SELECT AVG(score) FROM results WHERE year = 9;

SELECT AVG score FROM results; → The column name is missing brackets. An aggregate function needs brackets around the column, like AVG(score).

SELECT COUNT(*) FROM results WHERE score > 50 AVG; → AVG is stuck on the end of the query. A function must go in the SELECT part, such as SELECT AVG(score), not after WHERE.

Key Term

SELECT AVG(score) FROM results;

An aggregate function works out one value from many rows. COUNT, SUM, AVG, MIN and MAX give the count, total, mean, lowest and highest. This query returns the mean score.

Fill in the Blanks

1. SELECT COUNT(*) FROM results;

Hint: Type the aggregate function that counts how many rows there are.

2. SELECT SUM(score) FROM results;

Hint: Type the aggregate function that adds up all the values in a column.

3. SELECT AVG(score) FROM results;

Hint: Type the aggregate function that works out the mean of a column.

4. SELECT MIN(score), MAX(score) FROM results;

Hint: Type the aggregate function that finds the highest value.

5. SELECT COUNT(*) FROM results WHERE score > 50;

Hint: Type the keyword that filters rows before the function counts them.

Spot the Error

Question 1

A SELECT COUNT(*)

B FROM results

C WHERE subject = Maths;

Line C — Text values in SQL must be in quotes. Maths should be written as 'Maths', otherwise SQL thinks it is a column name.

Question 2

A SELECT SUM(price)

B FROM orders

C WHERE status == 'paid';

Line C — SQL uses a single = to compare values, not ==. It should be WHERE status = 'paid';

Question 3

A SELECT MIN(score)

B FROM results

C WHERE year = 10

D AND subject = 'Maths;

Line D — The text value is missing its closing quote. It should be AND subject = 'Maths';

Question 4

A SELECT AVERAGE(score)

B FROM results

C WHERE year = 9;

Line A — AVERAGE is not an SQL aggregate function. The function for the mean is AVG, so it should be SELECT AVG(score).

Question 5

A SELECT COUNT(*)

B FROM students

C WHERE age => 15;

Line C — The 'greater than or equal to' operator is >=, not =>. It should be WHERE age >= 15;

Student with a class code? Join your class →