SUM and COUNT with a WHERE condition
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which query counts only the members who live in Leeds?
2. A Member table has 40 records and none have Town = 'Paris'. What does SELECT COUNT(*) FROM Member WHERE Town = 'Paris'; output?
3. In SELECT SUM(Fee) FROM Member WHERE Town = 'York'; which records are added up?
4. Which query adds up the Fee for members aged over 18 only?
5. Which query correctly counts members from Leeds OR York?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1SELECT COUNT(*) FROM Member WHERE Town = 'Leeds'; | ACounts only the records where Town is Leeds, giving the number of members from Leeds. | |
| 2SELECT SUM(Fee) FROM Member WHERE Town = 'York'; | BAdds up the Fee values for York members only, ignoring members from other towns. | |
| 3Count the members who are from Leeds AND are aged over 16, using AND inside the WHERE clause of a COUNT. | CSELECT COUNT(*) FROM Member WHERE Town = 'Leeds' AND Age > 16; | |
| 4If no record matches the WHERE condition, COUNT returns zero, for example when no member lives in a town that is not in the table. | DThe WHERE clause is placed before FROM. FROM must come first, then WHERE: SELECT COUNT(*) FROM Member WHERE Town = 'Leeds'; | |
| 5SELECT COUNT(*) WHERE Town = 'Leeds' FROM Member; | EThe text value Leeds is missing quotes. It should be 'Leeds' because Town is a text field. | |
| 6SELECT SUM(Fee) FROM Member WHERE Town = Leeds; | FSELECT COUNT(*) FROM Member WHERE Town = 'Mars'; |
C
Definitions
SELECT COUNT(*) FROM Member WHERE Town = 'Leeds';
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
SELECT COUNT(*)
FROM Member
_____ Town = 'Leeds';
2.
SELECT ___(Fee)
FROM Member
WHERE Town = 'Leeds';
3.
SELECT COUNT(*)
FROM Member
WHERE Town = 'Leeds' ___ Age > 18;
4.
SELECT COUNT(*)
FROM Member
WHERE Town = 'Leeds' __ Town = 'York';
5.
SELECT _____(*)
FROM Member
WHERE Town = 'Atlantis';
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
SELECT SUM(Fee)
B
FROM Member
C
WHERE Town = 'Leeds'
D
AND Paid == TRUE;
Question 2
A
SELECT COUNT(*)
B
FROM Member
C
WHERE Town = Hull;
Question 3
A
SELECT SUM(*)
B
FROM Member
C
WHERE Age > 17;
Question 4
A
SELECT SUM(Fee)
B
FROM Member
C
WHERE Town IS 'York';
Question 5
A
SELECT SUM(Fee)
B
FROM Fee
C
WHERE Paid = TRUE;
Answer Key
SUM and COUNT with a WHERE condition
Section A: Quick Questions
Q1
B — SELECT COUNT(*) FROM Member WHERE Town = 'Leeds';
Q2
A — 0
Q3
B — Only the records where Town is York
Q4
A — SELECT SUM(Fee) FROM Member WHERE Age > 18;
Q5
A — SELECT COUNT(*) FROM Member WHERE Town = 'Leeds' OR Town = 'York';
Section B: Match the Pairs
Q1
SELECT COUNT(*) FROM Member WHERE Town = 'Leeds'; → Counts only the records where Town is Leeds, giving the number of members from Leeds.
Q2
SELECT SUM(Fee) FROM Member WHERE Town = 'York'; → Adds up the Fee values for York members only, ignoring members from other towns.
Q3
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;
Q4
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';
Q5
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';
Q6
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.
Section C: Definitions
Q1
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.Section D: Fill in the Blanks
Q1
SELECT COUNT(*)
FROM Member
WHERE Town = 'Leeds';Q2
SELECT SUM(Fee)
FROM Member
WHERE Town = 'Leeds';Q3
SELECT COUNT(*)
FROM Member
WHERE Town = 'Leeds' AND Age > 18;Q4
SELECT COUNT(*)
FROM Member
WHERE Town = 'Leeds' OR Town = 'York';Q5
SELECT COUNT(*)
FROM Member
WHERE Town = 'Atlantis';Section E: Spot the Error
Q1
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.
Q2
C — Text values in a WHERE condition must be in quotes. It should be WHERE Town = 'Hull'; so COUNT only counts members from Hull.
Q3
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(*).
Q4
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.
Q5
B — FROM must name the table, which is Member. Fee is a field, not a table. The line should be FROM Member.