Use a window partition for per-category totals

By Batu · English technical notes

Also published in our primary archive.

To retain every detail row while calculating a per-category total in SQLite, use SUM(amount) OVER (PARTITION BY category). For category A amounts 2 and 3 and category B amount 7, that returns totals 5, 5, and 7.

The naive query uses SUM(amount) OVER (). It is a valid window calculation, but the empty OVER clause puts every selected row in one partition, so each row gets the global total 12. That loses the category context the reader needs. A conventional GROUP BY category would produce one row per category instead, which is useful in other situations but cannot preserve the individual input amounts in this result.

The corrected query places category in PARTITION BY and adds an explicit final order for a stable display. The assertion checks both repeated A total values and the independent B total. SQLite describes partitions as rows sharing the PARTITION BY values and says processing occurs separately for each partition in its window-functions documentation. The fixture is created with the standard-library sqlite3 module, entirely in memory. No newer Python-specific API is used (Python 3.5+); window functions require SQLite 3.25.0+.

AI-assistance disclosure: this synthetic example was drafted with AI assistance and is intended to be run and adapted by the reader.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE sales(category TEXT, amount INTEGER)")
seed = [("A", 2), ("A", 3), ("B", 7)]
con.executemany("INSERT INTO sales VALUES (?, ?)", seed)

print("schema:", con.execute(
    "SELECT sql FROM sqlite_master WHERE name = 'sales'"
).fetchone()[0])
print("seed:", seed)
naive = list(con.execute("""
    SELECT category, amount, SUM(amount) OVER () AS total
    FROM sales ORDER BY category, amount
"""))
print("naive global total:", naive)
corrected = list(con.execute("""
    SELECT category, amount, SUM(amount) OVER (PARTITION BY category) AS total
    FROM sales ORDER BY category, amount
"""))
print("corrected partition total:", corrected)

assert corrected == [("A", 2, 5), ("A", 3, 5), ("B", 7, 7)]
con.close()

schema: CREATE TABLE sales(category TEXT, amount INTEGER)
seed: [('A', 2), ('A', 3), ('B', 7)]
naive global total: [('A', 2, 12), ('A', 3, 12), ('B', 7, 12)]
corrected partition total: [('A', 2, 5), ('A', 3, 5), ('B', 7, 7)]

Sources

SQLite SELECT