03.2 COUNT, SUM, AVG, MIN and MAX

01 | ONE RESULT FROM MANY ROWS

A per-row calculation produces one computed value for each row. An aggregate function summarises a collection of values, such as the total capacity across clubs.

Without GROUP BY, an aggregate query can return one summary row for the filtered input. The next lesson divides the data into groups.

Use this section’s three-table practice database. It is a fresh dataset, separate from Sections 01 and 02. All examples use SQLite. A membership score is an optional fictional activity result from 0 to 100; NULL means not yet recorded, not zero.

import sqlite3

connection = sqlite3.connect(":memory:")
setup_sql = """
PRAGMA foreign_keys = ON;

CREATE TABLE Students (
    student_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    year_level INTEGER NOT NULL
);
CREATE TABLE Clubs (
    club_id INTEGER PRIMARY KEY,
    club_name TEXT NOT NULL,
    category TEXT NOT NULL,
    capacity INTEGER NOT NULL,
    fee_cents INTEGER NOT NULL
);
CREATE TABLE Memberships (
    student_id INTEGER NOT NULL,
    club_id INTEGER NOT NULL,
    role TEXT NOT NULL,
    score INTEGER,
    PRIMARY KEY (student_id, club_id),
    FOREIGN KEY (student_id) REFERENCES Students(student_id),
    FOREIGN KEY (club_id) REFERENCES Clubs(club_id)
);

INSERT INTO Students VALUES
    (101, 'Maya', 10), (102, 'Leo', 11), (103, 'Amina', 10),
    (104, 'Noah', 12), (105, 'Maya', 11);
INSERT INTO Clubs VALUES
    (1, 'Robotics', 'Technology', 12, 1500),
    (2, 'Game Design', 'Technology', 16, 0),
    (3, 'Art', 'Creative', 12, 800),
    (4, 'Film', 'Creative', 20, 1000),
    (5, 'Chess', 'Strategy', 16, 0);
INSERT INTO Memberships VALUES
    (101, 1, 'member', 95), (101, 2, 'leader', 85),
    (102, 1, 'member', NULL), (103, 3, 'member', 70),
    (103, 4, 'leader', 90), (104, 2, 'member', 80);
"""
query = """
SELECT club_id, club_name FROM Clubs ORDER BY club_id;
"""

try:
    connection.executescript(setup_sql)
    cursor = connection.execute(query)
    print(" | ".join(column[0] for column in cursor.description))
    for row in cursor.fetchall():
        print(row)
except sqlite3.Error as error:
    print("Database error:", error)
finally:
    connection.close()

02 | REFERENCE DATA

03 | COUNT ALL ROWS

SELECT COUNT(*) AS membership_count
FROM Memberships;

COUNT(*) counts input rows, regardless of NULL attributes. There are six membership rows, not six distinct people: Maya 101 and Amina each have two memberships.

SELECT COUNT(*) AS student_count
FROM Students;

The table and joins determine the collection being counted. Choose them to match your question.

04 | COUNT A COLUMN’S RECORDED VALUES

SELECT COUNT(*) AS memberships,
       COUNT(score) AS recorded_scores
FROM Memberships;

COUNT(score) ignores NULL scores, so it is five. Leo’s membership exists but has no recorded score.

A score of zero would be a recorded value and would count. NULL is not a synonym for zero, so the difference is meaningful.

05 | COUNT DISTINCT PEOPLE

SELECT COUNT(DISTINCT student_id) AS participating_students
FROM Memberships;

DISTINCT removes repeated non-NULL student IDs before counting. Four students participate, while Students contains five records and Memberships contains six.

Counting DISTINCT first_name would answer a different and unreliable people-count question because names repeat.

06 | SUM ADDS VALUES

SELECT SUM(capacity) AS total_capacity
FROM Clubs;

The total is 12 + 16 + 12 + 20 + 16 = 76 planned places. This is not the current number of members or proof that every place can be used.

SUM ignores NULL inputs. Summing fee_cents here would add listed club fees once per club, not calculate actual revenue.

07 | AVG USES NON-NULL VALUES

SELECT SUM(score) AS score_total,
       COUNT(score) AS recorded_scores,
       AVG(score) AS average_score
FROM Memberships;

The recorded scores total 420. AVG divides by the five recorded values, giving 84. It does not divide by six memberships or substitute zero for the missing score.

Replacing NULL with zero would change the meaning and produce 70. Do that only when the underlying rule truly says missing should count as zero.

08 | MIN AND MAX FIND VALUES

SELECT MIN(capacity) AS smallest_capacity,
       MAX(capacity) AS largest_capacity
FROM Clubs;

The result gives values 12 and 20, not the complete rows of the clubs having them. More than one club can share a minimum or maximum.

Do not write a bare club_name beside MAX(capacity) and expect a portable, fully specified winning record. Later in this section, a subquery will retrieve matching rows explicitly.

09 | FILTER BEFORE SUMMARISING

SELECT COUNT(*) AS club_count,
       SUM(capacity) AS total_capacity,
       AVG(capacity) AS average_capacity
FROM Clubs
WHERE category = 'Technology';

WHERE chooses the input records before the aggregate is calculated. The two Technology clubs have capacity 28 in total and mean capacity 14.

SELECT COUNT(*) AS club_count, SUM(capacity) AS total_capacity,
       AVG(capacity) AS average_capacity, MIN(capacity) AS minimum_capacity
FROM Clubs
WHERE category = 'Sport';

No rows qualify. COUNT returns 0, while SUM, AVG and MIN return NULL here. COALESCE can display zero for an empty sum if that is the intended report meaning.

10 | JOINS CAN CHANGE YOUR TOTAL

SELECT c.club_id, c.capacity, m.student_id
FROM Clubs AS c
INNER JOIN Memberships AS m ON c.club_id = m.club_id
ORDER BY c.club_id, m.student_id;

Robotics’ capacity now appears twice because it has two members. Adding capacity across these joined rows would give 88, not the club-table total 76. Chess is absent, while some other capacities repeat.

SUM(DISTINCT capacity) is not a general repair: different clubs can legitimately share the same capacity. Sum at the level represented by the fact you need, or restructure the report.

11 | PRACTISE THE DIFFERENCE

SELECT COUNT(*) AS memberships,
       COUNT(DISTINCT student_id) AS students,
       COUNT(score) AS recorded_scores
FROM Memberships
WHERE club_id = 1;

SELECT MIN(score) AS lowest_score, MAX(score) AS highest_score,
       AVG(score) AS mean_score
FROM Memberships;

KEEP LEARNING

Previous page 03.3 GROUP BY and HAVING

References: SQLite joins and grouping · Aggregate functions · Subquery expressions