03.3 GROUP BY and HAVING

01 | ONE TOTAL IS NOT ALWAYS ENOUGH

The organiser wants capacity by category, not one number for the whole database. GROUP BY divides the input into groups so each group can produce its own summary.

Decide what one result row should mean: one category, one club or one year level. That decision guides the grouping columns.

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 | BUILD A GROUPED RESULT

SELECT category, COUNT(*) AS club_count,
       SUM(capacity) AS total_capacity
FROM Clubs
GROUP BY category
ORDER BY category;

Creative contains Art and Film, so it has two clubs and capacity 32. Technology contains Robotics and Game Design, giving two clubs and capacity 28. Strategy has Chess alone, giving 16.

GROUP BY is not an ordering promise. ORDER BY explicitly controls the display order.

04 | WALK THROUGH THE GROUPS

Grouping does not combine the stored records permanently. It builds result rows summarising the selected input.

Every selected column should be a grouping column or an aggregate in the clear, portable queries we write here. A bare club_name beside grouped category totals does not identify all names in the group.

05 | WHERE FILTERS THE INPUT ROWS

SELECT category, COUNT(*) AS club_count,
       SUM(capacity) AS total_capacity
FROM Clubs
WHERE fee_cents = 0
GROUP BY category
ORDER BY category;

Only Game Design and Chess enter this grouping. The Technology summary therefore counts one free club, not both Technology clubs.

Ask whether your condition concerns individual records or a group summary before choosing WHERE or HAVING.

06 | HAVING FILTERS THE GROUPS

SELECT category, COUNT(*) AS club_count,
       SUM(capacity) AS total_capacity
FROM Clubs
GROUP BY category
HAVING COUNT(*) >= 2
ORDER BY category;

All club rows enter the groups, then HAVING keeps groups containing at least two clubs. Creative and Technology remain; Strategy is removed.

WHERE COUNT(*) >= 2 does not express this task: the aggregate does not describe an individual source row at that stage.

07 | COMBINE WHERE AND HAVING

SELECT category, COUNT(*) AS paid_clubs,
       SUM(capacity) AS paid_capacity
FROM Clubs
WHERE fee_cents > 0
GROUP BY category
HAVING SUM(capacity) >= 20
ORDER BY category;

WHERE removes free clubs first. Of the remaining category summaries, only Creative reaches capacity 20 or more. Technology’s paid subset contains Robotics alone, capacity 12.

A helpful logical reading is FROM/JOIN → WHERE → GROUP BY → HAVING → result expressions → ORDER BY → LIMIT. This is a reasoning aid, not a physical execution guarantee.

08 | GROUP JOINED MEMBERSHIPS

SELECT c.club_id, c.club_name, COUNT(*) AS members
FROM Clubs AS c
INNER JOIN Memberships AS m ON c.club_id = m.club_id
GROUP BY c.club_id, c.club_name
ORDER BY c.club_id;

One result row now means one club with at least one membership. Grouping by ID and name keeps different clubs separate even if descriptive names could repeat.

Chess is absent because INNER JOIN never created an input row for it. GROUP BY cannot restore an input that was removed.

09 | INCLUDE ZERO-MEMBER CLUBS CORRECTLY

SELECT c.club_id, c.club_name,
       COUNT(m.student_id) AS member_count,
       COUNT(m.score) AS recorded_scores,
       AVG(m.score) AS average_score
FROM Clubs AS c
LEFT JOIN Memberships AS m ON c.club_id = m.club_id
GROUP BY c.club_id, c.club_name
ORDER BY c.club_id;

Chess gets one unmatched placeholder row, but m.student_id is NULL there, so COUNT(m.student_id) correctly gives zero. COUNT(*) would count that placeholder as one.

Chess’s average is NULL because it has no recorded scores. Robotics has two members but only one recorded score, giving an average of 95 for recorded scores. Do not read that as two equal scores.

10 | RETAIN ONLY GROUPS NEEDING ATTENTION

SELECT c.club_id, c.club_name, COUNT(m.student_id) AS members
FROM Clubs AS c
LEFT JOIN Memberships AS m ON c.club_id = m.club_id
GROUP BY c.club_id, c.club_name
HAVING COUNT(m.student_id) < 2
ORDER BY c.club_id;

Art, Film and Chess have fewer than two members in this dataset. The LEFT JOIN and non-NULL key count are both necessary to include zero-member clubs.

11 | INDEPENDENT PRACTICE

SELECT s.student_id, s.first_name, COUNT(m.club_id) AS memberships
FROM Students AS s
INNER JOIN Memberships AS m ON s.student_id = m.student_id
GROUP BY s.student_id, s.first_name
HAVING COUNT(m.club_id) >= 2
ORDER BY s.student_id;

SELECT category, SUM(capacity) AS total_capacity
FROM Clubs
GROUP BY category
HAVING SUM(capacity) > 30
ORDER BY category;

KEEP LEARNING

Previous page 03.4 Simple subqueries

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