SQLite Basics: Build Conditional Reports with Aggregate FILTER

A dashboard often needs several totals from the same rows: all tickets, open tickets, and completed work. An aggregate FILTER clause gives each calculation its own condition without removing rows from the rest of the report.

FILTER requires SQLite 3.30.0 or later. That version added it to aggregate functions, as documented in the release notes. These examples use counts and integer-cent totals, not a performance benchmark.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. 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 runner as run-sql.py. It prints pipe-separated rows and displays missing values as NULL. It does not escape embedded pipes or newlines, so it is a teaching runner, not a general SQL shell or CSV exporter. Put each statement’s ending semicolon at the end of a line, as shown; do not put two complete statements on one line.

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()

Each invocation starts with a fresh in-memory database. The examples are independent, and the toy data disappears when the connection closes.

1. Calculate several counts in one grouped report

Save as summarize-ticket-states.sql:

CREATE TABLE tickets (
    id INTEGER PRIMARY KEY,
    team TEXT NOT NULL,
    status TEXT NOT NULL,
    estimated_minutes INTEGER
);
INSERT INTO tickets VALUES
    (1, 'Blue', 'open', 30),
    (2, 'Blue', 'done', 60),
    (3, 'Blue', 'open', NULL),
    (4, 'Gold', 'done', 20);
SELECT team,
       COUNT(*) AS all_tickets,
       COUNT(*) FILTER (WHERE status = 'open') AS open_tickets,
       COUNT(*) FILTER (WHERE status = 'done') AS done_tickets,
       SUM(estimated_minutes) FILTER (WHERE status = 'open') AS open_minutes
FROM tickets
GROUP BY team
ORDER BY team;

Run:

python3 run-sql.py summarize-ticket-states.sql

Output:

Blue|3|2|1|30
Gold|1|0|1|NULL

Blue has three tickets, two of them open. Only one open ticket has an estimate, so the reported 30 minutes is a known subtotal, not proof that all open work has been estimated. Gold remains in the report even though it has no open tickets.

Moving the open-status condition into the query’s ordinary WHERE clause would discard completed rows before every aggregate. That would change all_tickets and done_tickets, not just open_minutes. Keep global row restrictions in WHERE and calculation-specific restrictions in FILTER.

2. Keep groups that have no matching details

Save as retain-empty-store-groups.sql:

CREATE TABLE stores (name TEXT PRIMARY KEY);
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    store TEXT NOT NULL,
    status TEXT NOT NULL,
    cents INTEGER NOT NULL
);
INSERT INTO stores VALUES ('East'), ('North'), ('West');
INSERT INTO orders VALUES
    (1, 'East', 'paid', 1200),
    (2, 'East', 'pending', 700),
    (3, 'West', 'pending', 500);
SELECT s.name,
       COUNT(o.id) AS order_count,
       COUNT(o.id) FILTER (WHERE o.status = 'paid') AS paid_count,
       COALESCE(SUM(o.cents) FILTER (WHERE o.status = 'paid'), 0) AS paid_cents
FROM stores AS s
LEFT JOIN orders AS o ON o.store = s.name
GROUP BY s.name
ORDER BY s.name;

Run:

python3 run-sql.py retain-empty-store-groups.sql

Output:

East|2|1|1200
North|0|0|0
West|1|0|0

The separate stores table supplies the groups, including North, which has no orders. COUNT(o.id) ignores the NULL detail row produced by the LEFT JOIN. COUNT(*) would instead report one row for North and mislabel it as an order.

The paid filter stays inside the aggregates. A final WHERE o.status = ‘paid’ would remove both North and West. Converting the missing paid sum to zero is deliberate here: cents is NOT NULL, and the report defines no paid orders as a zero paid total. Do not use that convention to disguise unknown amounts.

3. Count distinct values within each condition

Save as count-distinct-paying-customers.sql:

CREATE TABLE visits (
    id INTEGER PRIMARY KEY,
    channel TEXT NOT NULL,
    customer TEXT,
    purchased INTEGER NOT NULL CHECK (purchased IN (0, 1))
);
INSERT INTO visits VALUES
    (1, 'email', 'A', 1),
    (2, 'email', 'A', 1),
    (3, 'email', 'B', 0),
    (4, 'email', NULL, 1),
    (5, 'search', 'C', 0),
    (6, 'search', 'D', 1);
SELECT channel,
       COUNT(*) FILTER (WHERE purchased = 1) AS purchase_visits,
       COUNT(DISTINCT customer) FILTER (WHERE purchased = 1) AS known_buyers,
       COUNT(*) FILTER (WHERE purchased = 1 AND customer IS NULL) AS anonymous_purchases
FROM visits
GROUP BY channel
ORDER BY channel;

Run:

python3 run-sql.py count-distinct-paying-customers.sql

Output:

email|3|1|1
search|1|1|0

Email produced three purchase visits, but only one distinct known buyer. Customer A’s second purchase visit does not increase the distinct count. The anonymous purchase remains visible in its own column instead of disappearing silently from the report.

These columns answer different questions: visits, identified people, and unidentified purchase visits. Naming them precisely matters more than making every count look the same. A NULL customer is not a shared customer identity, and COUNT(DISTINCT customer) does not count it as one.

The aggregate function reference documents FILTER, DISTINCT, and each aggregate’s NULL behavior. Build conditional reports by first defining what one output row represents, then deciding which source rows belong to each calculation. Keep an explicit ORDER BY for the presentation order.

Clean up

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

rm -- run-sql.py summarize-ticket-states.sql retain-empty-store-groups.sql count-distinct-paying-customers.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.