Explain why SUM of no rows is NULL

By Batu · English technical notes

For an empty filtered set, SQLite SUM(amount) is NULL, not numeric zero. Use total(amount) when SQLite's always-numeric 0.0 empty-input result is appropriate, or use COALESCE(SUM(amount), 0) when the application explicitly wants a fallback. SQLite aggregate functions documents the different contracts, and Python sqlite3 documents the in-memory connection API.

The fixture has two amounts, but neither belongs to missing. It prints the schema and seed rows before running the naive aggregate. The first result is Python None, SQLite's representation of SQL NULL. The corrected query retrieves both functions from the same empty filtered input, making the contrast testable: SUM remains NULL; total is 0.0.

The assertions establish only this synthetic query's return values. They do not decide whether a missing total means zero in a particular domain; for example, no matching invoices and a known zero invoice balance may require different application handling. sqlite3 is part of the standard library. This example uses f-strings, so it requires Python 3.6+.

AI assistance disclosure: AI helped draft this article; the database fixture is synthetic and runs locally.

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE sales (category TEXT, amount INTEGER)")
seed_rows = [("present", 12), ("present", 8)]
connection.executemany(
    "INSERT INTO sales(category, amount) VALUES (?, ?)", seed_rows
)

naive = connection.execute(
    "SELECT SUM(amount) FROM sales WHERE category = 'missing'"
).fetchone()[0]
corrected = connection.execute(
    "SELECT SUM(amount), total(amount) FROM sales WHERE category = 'missing'"
).fetchone()

assert naive is None
assert corrected == (None, 0.0)
print("schema: sales(category TEXT, amount INTEGER)")
print(f"seed rows: {seed_rows}")
print(f"naive SUM result: {naive!r}")
print(f"corrected result: SUM={corrected[0]!r}, total={corrected[1]}")

schema: sales(category TEXT, amount INTEGER)
seed rows: [('present', 12), ('present', 8)]
naive SUM result: None
corrected result: SUM=None, total=0.0