Avoid a join multiplication error by aggregating first

By Batu · English technical notes

Also published in our primary archive.

Joining two independent one-to-many relationships before calculating totals can multiply rows. The example has one product, two sales, and two resupplies. A direct join pairs each sale with each resupply, yielding four rows. Consequently, SUM(s.amount) becomes 10 instead of 5 and SUM(r.amount) becomes 24 instead of 12. The first asserted result intentionally demonstrates that faulty calculation.

Aggregate each detail table to one row per parent first. The sold and stocked common table expressions each use GROUP BY product_id and SUM(amount), then the outer query joins those per-product totals. Because each CTE supplies one row for the product in this fixture, the final join does not create the sales-by-resupplies Cartesian pairing. The second assertion verifies the intended (5, 12) totals and the output shows both tuples.

This pattern applies when the measures are independent. It does not by itself decide how to represent a product missing sales or missing resupplies: an inner join will omit it, whereas left joins plus carefully chosen display or arithmetic handling may be appropriate. It also does not replace checking the query plan or indexes on realistic data. SQLite documents aggregate functions and common table expressions; the Python standard library documents the sqlite3 connection shortcuts used here in its reference. No newer API is used; Python 3.6+ is needed for f-strings. AI assistance disclosure: this article was drafted with AI assistance.

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript(
    "CREATE TABLE product (id INTEGER PRIMARY KEY, name TEXT);"
    "CREATE TABLE sale (product_id INTEGER, amount INTEGER);"
    "CREATE TABLE resupply (product_id INTEGER, amount INTEGER);"
)
con.execute("INSERT INTO product VALUES (1, 'tea')")
con.executemany("INSERT INTO sale VALUES (1, ?)", [(3,), (2,)])
con.executemany("INSERT INTO resupply VALUES (1, ?)", [(5,), (7,)])

multiplied = con.execute(
    "SELECT SUM(s.amount), SUM(r.amount) "
    "FROM product AS p "
    "JOIN sale AS s ON s.product_id = p.id "
    "JOIN resupply AS r ON r.product_id = p.id"
).fetchone()
aggregated_first = con.execute(
    "WITH sold AS ("
    "  SELECT product_id, SUM(amount) AS total FROM sale GROUP BY product_id"
    "), stocked AS ("
    "  SELECT product_id, SUM(amount) AS total FROM resupply GROUP BY product_id"
    ") "
    "SELECT sold.total, stocked.total "
    "FROM product AS p "
    "JOIN sold ON sold.product_id = p.id "
    "JOIN stocked ON stocked.product_id = p.id"
).fetchone()

assert multiplied == (10, 24)
assert aggregated_first == (5, 12)
print(f"multiplied={multiplied}; aggregated_first={aggregated_first}")
con.close()

multiplied=(10, 24); aggregated_first=(5, 12)