How many games can be played online? What is the total price of the adventure games? SUM and COUNT turn a list of matching records into a single number, and AND and OR let one WHERE clause test two conditions at once.
SUM(field) adds up the values in a numeric field for every record that matches the WHERE condition. COUNT(field) gives the number of records that match. Both return a single value, not a list. In a WHERE clause, AND keeps a record only if both conditions are true; OR keeps a record if at least one condition is true. A record that meets both OR conditions is still output only once.
Fig. 1: three records match the condition; COUNT gives 3 and SUM adds their prices
Section 01
SUM and COUNT.
In the lab, run a SUM or COUNT query on the Books table and compare it with the same query without them: the list of rows collapses into one number. In this lesson every query again uses the GAMES table.
GameID
Title
Genre
Price
Year
Online
G01
Star Racer
Racing
25.00
2021
TRUE
G02
Castle Quest
Adventure
40.00
2019
FALSE
G03
Puzzle Pop
Puzzle
10.00
2023
TRUE
G04
Speed Kings
Racing
35.00
2022
TRUE
G05
Dragon Isle
Adventure
30.00
2020
FALSE
G06
Block Town
Puzzle
15.00
2018
FALSE
Worked example SUM
Give the output of this query.
SELECT SUM(Price)
FROM Games
WHERE Genre = 'Adventure';
Find the Adventure records: G02 (40.00) and G05 (30.00).
Add their prices: 40.00 + 30.00.
Answer: 70.00
Worked example COUNT
Give the output of this query.
SELECT COUNT(GameID)
FROM Games
WHERE Online = TRUE;
Check Online for each record: G01 TRUE, G02 FALSE, G03 TRUE, G04 TRUE, G05 FALSE, G06 FALSE.
Three records match; COUNT gives how many, not which ones.
Answer: 3
Your turn
Give the output of: SELECT SUM(Price) FROM Games;
No WHERE, so every record: 25 + 40 + 10 + 35 + 30 + 15 = 155.00
Examiner trap
SUM adds the values; COUNT counts the records. COUNT(Price) on the whole GAMES table gives 6, not 155.00.
Section 02
Two conditions: AND and OR.
Write the full condition on each side of AND or OR: Genre = 'Racing' AND Price > 30, not Genre = 'Racing' AND > 30. Then test each record against both parts.
Condition 1
Condition 2
AND
OR
false
false
not output
not output
false
true
not output
output
true
false
not output
output
true
true
output
output
Worked example AND
Give the output.
SELECT Title
FROM Games
WHERE Genre = 'Racing' AND Price > 30;
The Racing records are G01 (25.00) and G04 (35.00).
Only G04 also has Price > 30. G01 fails the second condition.
Answer: Speed Kings
Worked example OR
Give the output.
SELECT Title
FROM Games
WHERE Genre = 'Puzzle' OR Year < 2020;
Puzzle: G03 Puzzle Pop and G06 Block Town.
Year < 2020: G02 Castle Quest (2019) and G06 Block Town (2018).
Block Town meets both but appears once. Output in table order.
Answer: Castle Quest; Puzzle Pop; Block Town
Your turn
Give the output of: SELECT COUNT(GameID) FROM Games WHERE Price > 20 AND Year > 2019;
Price > 20: G01, G02, G04, G05. Of these, Year > 2019: G01 (2021), G04 (2022), G05 (2020). 3
Examiner trap
AND gives the same or fewer records than either condition alone; OR gives the same or more. If your AND answer has more rows than one of its conditions, recheck it.
Stage 1 · Learn
Check what the sim just showed you
Four quick checks. Each correct answer earns XP and records this skill on your profile.
Quick check+10 XP
What does COUNT(GameID) return in an SQL query?
Quick check+10 XP
In the lab, what is the output of SELECT COUNT(BookID) FROM Books WHERE Genre = 'Sci-fi';?
Quick check+10 XP
A WHERE clause joins two conditions with AND. When is a record output?
Quick check+10 XP
What is the output of SELECT SUM(Price) FROM Games WHERE Genre = 'Puzzle'; on the GAMES table?
Section 03
Putting it together.
Exam questions combine everything: a function, two conditions and sometimes a sort. Use the lab's practice questions to check your method, then work through the example below one record at a time.
SELECT SUM(Price)
FROM Games
WHERE Online = TRUE AND Price < 30;