SQLite Basics: Summarize with GROUP BY and HAVING

A summary report turns many input rows into fewer result rows. GROUP BY defines the buckets; aggregate functions calculate a value for each bucket. Think about the unit of a result row before choosing its columns: is it one sale, one team, or one month?

The inline datasets below keep each example independent. Amounts use integer cents, which makes these small totals exact without introducing floating-point currency calculations.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. No packages are needed. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this small runner as run-sql.py. It executes SQL against a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. Put each statement’s ending semicolon at the end of its own line, as below; this is a teaching runner, not a general SQL shell or CSV exporter.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

1. Count and total each team

Save as team-totals.sql:

WITH sales(team, cents) AS (
  VALUES ('North', 1200), ('South', 700), ('North', 800)
)
SELECT team, COUNT(*) AS sale_count, SUM(cents) AS total_cents
FROM sales
GROUP BY team
ORDER BY team;

Run:

python3 run-sql.py team-totals.sql

Output:

North|2|2000
South|1|700

The two North rows become one result row with a count of two and a total of 2000. South forms its own group. The output order is explicit; grouping itself is not a presentation-order promise.

Keep selected columns either grouped or aggregated. SQLite permits some ungrouped, non-aggregated columns, but the chosen value may not be the one you intend. Adding an individual sale label to this summary would blur what a result row means.

2. Filter rows, then filter groups

Save as qualifying-teams.sql:

WITH sales(team, cents, status) AS (
  VALUES ('North', 1200, 'paid'), ('North', 800, 'paid'),
         ('South', 700, 'paid'), ('South', 5000, 'cancelled'),
         ('West', 1800, 'paid')
)
SELECT team, SUM(cents) AS paid_cents
FROM sales
WHERE status = 'paid'
GROUP BY team
HAVING SUM(cents) >= 1500
ORDER BY paid_cents DESC, team;

Run:

python3 run-sql.py qualifying-teams.sql

Output:

North|2000
West|1800

WHERE removes the cancelled sale before it can affect South’s total. HAVING keeps only groups whose paid total reaches the threshold. This separation makes a report’s business rules easier to audit.

West passes with a single sale; North passes with two. The count of source rows is irrelevant unless you add a count condition. Use a deterministic secondary ordering, such as the team name, when totals can tie.

3. Distinguish missing amounts from zero

Save as missing-totals.sql:

WITH sales(team, cents) AS (
  VALUES ('North', 1200), ('North', NULL), ('South', NULL)
)
SELECT team, COUNT(*) AS rows_seen, COUNT(cents) AS known_amounts,
       SUM(cents) AS total_cents,
       COALESCE(SUM(cents), 0) AS display_cents
FROM sales
GROUP BY team
ORDER BY team;

Run:

python3 run-sql.py missing-totals.sql

Output:

North|2|1|1200|1200
South|1|0|NULL|0

COUNT(*) counts rows, while COUNT(cents) counts known amounts. SUM skips missing values and yields NULL when a group has no known amount. North therefore has two rows but only one amount contributing to its total.

The final column shows how to display zero deliberately. Keep the original total or a known-value count when zero could hide incomplete data. Also remember that a team with no input rows does not appear in this grouped report; creating empty groups requires a separate list of teams.

The official aggregate function reference documents counting and missing-value behavior. Try adding a real zero amount to South and compare the known-value count with the all-missing case.

Clean up

In the same terminal, remove the example files and the empty scratch directory:

rm -- run-sql.py team-totals.sql qualifying-teams.sql missing-totals.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.