SQL: SUM & COUNT

🎯 Syllabus & Goals 3 min

Cambridge 9.1 · SQL scripts to query data — SUM, COUNT Paper 2 · Algorithms, Programming and Logic

By the end of this lesson you can:

1. Use SUM to total a numeric field, with or without a WHERE condition.
2. Use COUNT to count the records that match a condition.
3. Decide whether a question needs SUM or COUNT, and state the single value returned.

Textbook: Chapter 9, §9.1.4 (pp. 344–348)

Recap / Warm-Up 5 min

Last lesson you filtered with AND, OR, NOT, BETWEEN, IN and LIKE. Here is FILM once more.

Quick starter

Without SQL: how many Animation films are there, and how many copies of them does the shop hold in total?

🧠 Key Concept 14 min

1 · Two questions a list cannot answer

Some questions need one number, not a list of records. "How many copies do we hold?" is a total. "How many films cost under 3.00?" is a count.

2 · SUM

With no WHERE, SUM totals the whole field — every copy of every film:

SELECT SUM(Copies)
FROM FILM;

With a WHERE, only matching records are added. Total running time of Sci-Fi and Action films:

SELECT SUM(Minutes)
FROM FILM
WHERE Genre IN ('Sci-Fi', 'Action');

3 · COUNT

COUNT(Field) counts the records that pass the WHERE test. Counting the primary key is a safe choice, because every record has one. COUNT(*) counts records too:

SELECT COUNT(*)
FROM FILM;

Worked Example 12 min

(a) "How many copies of Animation films are held?"

1. Decide: SUM or COUNT? "How many copies" asks for a total of the Copies values → SUM.Counting records would give 2 films, not the number of copies.
2. SELECT SUM(Copies)Copies is an integer field, so it can be added.
3. FROM FILM
4. WHERE Genre = 'Animation';
5. Matching records: F02 (6) and F09 (8). Add: 6 + 8 = 14.

SELECT SUM(Copies)
FROM FILM
WHERE Genre = 'Animation';

(b) "How many films cost less than 3.00?"

1. Decide: the answer is a number of films (records) → COUNT.
2. SELECT COUNT(FilmID)FilmID is the primary key, so every record has a value to count.
3. FROM FILM
4. WHERE Price < 3.00;Strictly less than: a film at exactly 3.00 would not count.
5. Matching records: F02 2.99, F04 1.99, F08 1.49, F10 2.49 → 4.

SELECT COUNT(FilmID)
FROM FILM
WHERE Price < 3.00;

(c) COUNT with BETWEEN

SELECT COUNT(Title)
FROM FILM
WHERE ReleaseYear BETWEEN 2020 AND 2022;

1. Years 2020, 2021 and 2022 all count — BETWEEN includes both ends.
2. F01 2021, F05 2022, F06 2020, F09 2022, F10 2021 → 5.F02 (2019) and F03/F07 (2023) are outside.

Try It Yourself 12 min

Tasks use the BAKERY table in Exam Practice below.

📝 Exam Practice 10 min

A bakery records each item and how many were sold today in the table BAKERY.

SELECT SUM(SoldToday)
FROM BAKERY
WHERE Category = 'Pie';

SELECT COUNT(ItemCode)
FROM BAKERY
WHERE Price > 2.50 AND SoldToday >= 10;

SELECT COUNT(ItemCode)
FROM BAKERY
WHERE Category = 'Cake';

Recap & Key Terms 3 min

SUM and COUNT go in the SELECT clause and return one value. SUM adds numbers in a field; COUNT counts matching records. A WHERE clause decides which records take part.

SUM

Returns the sum of all the values in a field (column); used with SELECT. The field must be integer or real.

COUNT

Counts the number of records (rows) where the field matches a specified condition; used with SELECT.

Result set

The data a query returns — here, a single value.

Condition

The test in a WHERE clause that decides which records are included.

Homework 1 min

Task (≤ 15 min): Using BAKERY, show the output of this query and write one sentence saying what it tells the owner.

SELECT SUM(SoldToday)
FROM BAKERY
WHERE Category <> 'Loaf';