← Years 7–8 Data & Information

Counting and summarising with a query

A query function like COUNT, SUM, AVG, MIN or MAX that looks at many records and gives one answer, such as how many records match or the average of a field.

Part of: 7-8 Digital Technologies: Analysing and visualising data — Module 15

Understanding Counting and summarising with a query in Years 7–8 Data & Information

What it does: COUNT(*) with a WHERE criteria counts only the matching records, so only the dogs are counted. AVG(Price) summarises the Price field across all records and returns a single average. An aggregate function looks at many records and gives back ONE answer. 'How many' is a job for COUNT(*). 'Average' needs AVG on a field such as Age. AVG adds up the Score values and divides by how many there are, giving one answer for the whole field. A query needs FROM before the table name. The correct version is SELECT COUNT(*) FROM Students WHERE Year = 8.

Common mistakes: Text values in a query criterion need quote marks. Without them, Dog looks like a field name. It should be AND Pet = 'Dog';. A query criterion uses a single = to compare values, not ==. It should be WHERE Subject = 'Maths';.

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 Counting and summarising with a query to a class →

This module's key terms are also in a printable crossword (Modules 47–80) and word search (Modules 9–80).

Take this worksheet further — free with Cloodit

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

Learning

Duo

Which query function tells you HOW MANY records match your criteria?

COUNT(*)AVG(Score)

Matches

SELECT AVG(*) FROM Results

↓

AVG needs a field name to average, such as AVG(Score). The star only works with COUNT, which counts records.

Applying

Meanings

Term

aggregate function

Meaning

A query function like COUNT, SUM, AVG, MIN or MAX that looks at many records and gives one answer, such as how many records match or the average of a field.

Practice

SELECT COUNT(*), AVG(Score)

Testing

Gaps

SELECT (*) FROM Pets;

Check Answer

Spots

3WHERE Year = 8

4AND Pet = Dog;

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 function tells you HOW MANY records match your criteria?

A. AVG(Score)

B. COUNT(*) ✓

COUNT(*) counts the matching records. AVG works out the average of a field instead.

2. A query uses AVG(Price) on a Games table. What does it give you?

A. One number: the average price of all the games ✓

B. A list showing every game's price

An aggregate function looks at many records and gives back ONE answer.

3. Which query would answer 'How many Year 7 students are in the Students table?'

A. SELECT COUNT(*) FROM Students WHERE Year = 7 ✓

B. SELECT AVG(*) FROM Students WHERE Year = 7

To find how many records match, use COUNT(*) with a WHERE criterion. Averages need a number field, not *.

4. True or false: COUNT, SUM, AVG, MIN and MAX are all aggregate functions.

A. True ✓

B. False

Each one looks at many records and gives one answer, so they are all aggregate functions.

5. You want the average test mark from a Results table. Which is correct?

A. SELECT COUNT(Mark) FROM Results

B. SELECT AVG(Mark) FROM Results ✓

AVG(Mark) adds up the marks and divides by the number of records. COUNT only tells you how many there are.

Match the Pairs

SELECT COUNT(*) FROM Students WHERE Year = 7 → Counts how many records in Students have a Year of 7 and gives back one number

SELECT AVG(Score) FROM Results → Works out the average of all the values in the Score field

Find the total number of records in a Pets table that are dogs → SELECT COUNT(*) FROM Pets WHERE Type = 'Dog'

Find the average price of every item in a Canteen table → SELECT AVG(Price) FROM Canteen

SELECT AVG(*) FROM Results → AVG needs a field name to average, such as AVG(Score). The star only works with COUNT, which counts records.

SELECT COUNT(*) Students WHERE Year = 8 → The FROM keyword is missing, so the query doesn't say which table to count. It should be FROM Students.

Key Term

aggregate function

A query function like COUNT, SUM, AVG, MIN or MAX that looks at many records and gives one answer, such as how many records match or the average of a field.

Fill in the Blanks

1. SELECT COUNT(*) FROM Pets;

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

2. SELECT COUNT(*) FROM Pets WHERE Age > 5;

Hint: Type the keyword that picks only the matching records before counting.

3. SELECT AVG(Score) FROM Results;

Hint: Type the function that finds the average of a field.

4. SELECT COUNT(*) FROM Games;

Hint: Type the symbol that means count every record.

5. SELECT MAX(Score) FROM Results WHERE Year = 7;

Hint: Type the function that finds the highest value in a field.

Spot the Error

Question 1

A SELECT COUNT(*)

B FROM Students

C WHERE Year = 8

D AND Pet = Dog;

Line D — Text values in a query criterion need quote marks. Without them, Dog looks like a field name. It should be AND Pet = 'Dog';

Question 2

A SELECT AVG(Score)

B FROM Tests

C WHERE Subject == 'Maths';

Line C — A query criterion uses a single = to compare values, not ==. It should be WHERE Subject = 'Maths';

Question 3

A SELECT AVG(Age)

B FROM Players

C WHERE Team = 'Reds'

D AND Age => 12;

Line D — The 'greater than or equal to' operator is written >=, not =>. It should be AND Age >= 12;

Question 4

A SELECT COUNT(*

B FROM Library

C WHERE Genre = 'Sci-Fi';

Line A — The COUNT function is missing its closing bracket. It should be SELECT COUNT(*)

Question 5

A SELECT

B SUM[Goals]

C FROM Matches

D WHERE Team = 'Eagles';

Line B — Aggregate functions use round brackets around the field name, not square brackets. It should be SUM(Goals)

Student with a class code? Join your class →