Skip to content
SQLite: Aggregate child tables before joining totals

SQLite: Aggregate child tables before joining totals

November 16, 2026

A customer report that joins sales and refunds can overstate both totals. Reduce each independent child table to one row per customer before joining those totals.

Set up and run

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 the standard mktemp, rm and rmdir utilities. No additional packages or SQLite CLI are needed. Keep the same terminal open through cleanup. Stop if setup fails before saving the source.

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

Save as join-totals.py:

import sqlite3

db = sqlite3.connect(":memory:")
try:
    db.executescript("""
        CREATE TABLE customers (id INTEGER PRIMARY KEY);
        CREATE TABLE sales (id INTEGER PRIMARY KEY, customer_id INTEGER, cents INTEGER NOT NULL);
        CREATE TABLE refunds (id INTEGER PRIMARY KEY, customer_id INTEGER, cents INTEGER NOT NULL);
        INSERT INTO customers VALUES (1), (2);
        INSERT INTO sales VALUES (10, 1, 1000), (11, 1, 1000);
        INSERT INTO refunds VALUES (20, 1, 100), (21, 1, 200), (22, 1, 300);
    """)
    multiplied = db.execute("""
        SELECT COUNT(*), SUM(s.cents), SUM(r.cents), SUM(DISTINCT s.cents)
        FROM sales AS s JOIN refunds AS r ON r.customer_id = s.customer_id
        WHERE s.customer_id = 1
    """).fetchone()
    print("joined rows, sales, refunds, distinct sales:", multiplied)
    totals = db.execute("""
        WITH sales_totals AS (
            SELECT customer_id, SUM(cents) AS cents FROM sales GROUP BY customer_id
        ), refund_totals AS (
            SELECT customer_id, SUM(cents) AS cents FROM refunds GROUP BY customer_id
        )
        SELECT c.id, COALESCE(s.cents, 0), COALESCE(r.cents, 0)
        FROM customers AS c
        LEFT JOIN sales_totals AS s ON s.customer_id = c.id
        LEFT JOIN refund_totals AS r ON r.customer_id = c.id
        ORDER BY c.id
    """).fetchall()
    assert multiplied == (6, 6000, 1200, 1000)
    assert totals == [(1, 2000, 600), (2, 0, 0)]
    print("customer, sales, refunds:", totals)
finally:
    db.close()

Run:

python3 join-totals.py

Observed output:

joined rows, sales, refunds, distinct sales: (6, 6000, 1200, 1000)
customer, sales, refunds: [(1, 2000, 600), (2, 0, 0)]

Explain the six joined rows

Customer 1 has two sales and three refunds. Matching only on customer ID pairs every sale with every refund, producing six rows. Each sale appears three times, so the sales total becomes 6000 cents. Each refund appears twice, so the refund total becomes 1200 cents.

The two sales are separate records that both happen to be worth 1000 cents. SUM(DISTINCT s.cents) collapses their equal amounts and returns 1000, losing a legitimate sale. DISTINCT on an aggregate removes duplicate input values, not duplicate business records; see SQLite’s aggregate reference.

Each common table expression groups by customer ID first. The final joins therefore have at most one sales total and one refund total per customer. The resulting 2000 and 600 cents match the original records, and customer 2 stays in the report with zero totals.

Define the report’s unit of grouping

The example uses customer ID as the reporting key. If the report is per customer and month, both aggregations and the joins need that same pair of keys. Joining on only part of the grouping key can introduce multiplication again.

COALESCE represents a missing total as zero here. Both amount columns are NOT NULL, so a missing aggregate row means the customer has no records in that child table. If amounts can be unknown, decide whether substituting zero would hide missing data.

Use this pattern when the two child collections are independent. If a refund belongs to a particular sale, a report about that relationship needs the actual sale key and its own aggregation rules. SQLite’s join processing reference explains how join inputs are combined.

The assertions check the toy result, not query performance. All data is in memory; benchmark indexes and query plans separately with representative data.

Clean up

In the same terminal, remove only the example file and the empty scratch directory. If the directory is not empty, inspect what remains before deleting anything else.

rm -- join-totals.py
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.

Last updated on