← IGCSE Computer Science Paper 2

SUM and COUNT with a WHERE condition

WHERE filters the records first, then COUNT or SUM works only on the matching ones. This counts members from Leeds. If no record matches, COUNT gives 0.

Part of: IGCSE Computer Science 0478 — 9 Databases — Module 31, WA Computer Science ATAR Year 11 — Data Management — Module 72

Understanding SUM and COUNT with a WHERE condition in IGCSE Computer Science Paper 2

What it does: AND inside WHERE means a record must meet both conditions before COUNT includes it. No record has Town = 'Mars', so no records are counted and the result is 0. No records match the WHERE condition, so COUNT has nothing to count and gives 0. It does not give an error or the total. WHERE is applied before SUM, so the function only adds the Fee values of the matching records. The condition belongs in the WHERE clause, after FROM. The SUM brackets only hold the field to add up. A member lives in only one town, so AND would match no one and give 0. OR counts members in either town.

Common mistakes: SQL uses a single = to compare values, not ==. The line should be AND Paid = TRUE; so only paid members from Leeds are added up. Text values in a WHERE condition must be in quotes. It should be WHERE Town = 'Hull'; so COUNT only counts members from Hull.

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 SUM and COUNT with a WHERE condition to a class →

This module's key terms are also in a printable crossword (Modules 525–538) and word search (Modules 500–538).

Take this worksheet further — free with Cloodit

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

Learning

Duo

Which query counts only the members who live in Leeds?

SELECT COUNT(*) FROM Member WHERE Town = 'Leeds';SELECT COUNT(*) FROM Member;

Matches

SELECT COUNT(*) WHERE Town = 'Leeds' FROM Member;

↓

The WHERE clause is placed before FROM. FROM must come first, then WHERE: SELECT COUNT(*) FROM Member WHERE Town = 'Leeds';

Applying

Meanings

Term

SELECT COUNT(*) FROM Member WHERE Town = 'Leeds';

Meaning

WHERE filters the records first, then COUNT or SUM works only on the matching ones. This counts members from Leeds. If no record matches, COUNT gives 0.

Practice

SELECT COUNT(*) FROM students WHERE house = 'Red' AND points > 100;

Testing

Gaps

Town = 'Leeds';

Check Answer

Spots

3WHERE Town = 'Leeds'

4AND Paid == TRUE;

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 only the members who live in Leeds?

A. SELECT COUNT(*) FROM Member;

B. SELECT COUNT(*) FROM Member WHERE Town = 'Leeds'; ✓

WHERE filters the records first, so COUNT only counts the Leeds members. Without WHERE, COUNT counts every record.

2. A Member table has 40 records and none have Town = 'Paris'. What does SELECT COUNT(*) FROM Member WHERE Town = 'Paris'; output?

A. 0 ✓

B. 40

No records match the WHERE condition, so COUNT has nothing to count and gives 0. It does not give an error or the total.

3. In SELECT SUM(Fee) FROM Member WHERE Town = 'York'; which records are added up?

A. All records, then the York total is picked out

B. Only the records where Town is York ✓

WHERE is applied before SUM, so the function only adds the Fee values of the matching records.

4. Which query adds up the Fee for members aged over 18 only?

A. SELECT SUM(Fee) FROM Member WHERE Age > 18; ✓

B. SELECT SUM(Fee > 18) FROM Member;

The condition belongs in the WHERE clause, after FROM. The SUM brackets only hold the field to add up.

5. Which query correctly counts members from Leeds OR York?

A. SELECT COUNT(*) FROM Member WHERE Town = 'Leeds' OR Town = 'York'; ✓

B. SELECT COUNT(*) FROM Member WHERE Town = 'Leeds' AND Town = 'York';

A member lives in only one town, so AND would match no one and give 0. OR counts members in either town.

Match the Pairs

SELECT COUNT(*) FROM Member WHERE Town = 'Leeds'; → Counts only the records where Town is Leeds, giving the number of members from Leeds.

SELECT SUM(Fee) FROM Member WHERE Town = 'York'; → Adds up the Fee values for York members only, ignoring members from other towns.

Count the members who are from Leeds AND are aged over 16, using AND inside the WHERE clause of a COUNT. → SELECT COUNT(*) FROM Member WHERE Town = 'Leeds' AND Age > 16;

If no record matches the WHERE condition, COUNT returns zero, for example when no member lives in a town that is not in the table. → SELECT COUNT(*) FROM Member WHERE Town = 'Mars';

SELECT COUNT(*) WHERE Town = 'Leeds' FROM Member; → The WHERE clause is placed before FROM. FROM must come first, then WHERE: SELECT COUNT(*) FROM Member WHERE Town = 'Leeds';

SELECT SUM(Fee) FROM Member WHERE Town = Leeds; → The text value Leeds is missing quotes. It should be 'Leeds' because Town is a text field.

Key Term

SELECT COUNT(*) FROM Member WHERE Town = 'Leeds';

WHERE filters the records first, then COUNT or SUM works only on the matching ones. This counts members from Leeds. If no record matches, COUNT gives 0.

Fill in the Blanks

1. SELECT COUNT(*) FROM Member WHERE Town = 'Leeds';

Hint: Type the keyword that filters the records before they are counted.

2. SELECT SUM(Fee) FROM Member WHERE Town = 'Leeds';

Hint: Type the function that adds up the Fee values of only the matching records.

3. SELECT COUNT(*) FROM Member WHERE Town = 'Leeds' AND Age > 18;

Hint: Type the logical operator that makes both conditions true for a record to be counted.

4. SELECT COUNT(*) FROM Member WHERE Town = 'Leeds' OR Town = 'York';

Hint: Type the logical operator that counts a record if either condition is true.

5. SELECT COUNT(*) FROM Member WHERE Town = 'Atlantis';

Hint: Type the function that counts the matching records. With no matching town, it gives zero.

Spot the Error

Question 1

A SELECT SUM(Fee)

B FROM Member

C WHERE Town = 'Leeds'

D AND Paid == TRUE;

Line D — SQL uses a single = to compare values, not ==. The line should be AND Paid = TRUE; so only paid members from Leeds are added up.

Question 2

A SELECT COUNT(*)

B FROM Member

C WHERE Town = Hull;

Line C — Text values in a WHERE condition must be in quotes. It should be WHERE Town = 'Hull'; so COUNT only counts members from Hull.

Question 3

A SELECT SUM(*)

B FROM Member

C WHERE Age > 17;

Line A — SUM adds up the values in a numeric field, so it cannot be used with *. To count the records that match the WHERE condition, use SELECT COUNT(*).

Question 4

A SELECT SUM(Fee)

B FROM Member

C WHERE Town IS 'York';

Line C — A WHERE condition on a field uses a comparison operator such as =. IS is not used here. It should be WHERE Town = 'York'; so only York fees are summed.

Question 5

A SELECT SUM(Fee)

B FROM Fee

C WHERE Paid = TRUE;

Line B — FROM must name the table, which is Member. Fee is a field, not a table. The line should be FROM Member.

Student with a class code? Join your class →