Use FILTER with an aggregate for a conditional count

By Batu · English technical notes

Also published in our primary archive.

Use COUNT(*) FILTER (WHERE state = 'paid') inside the grouped query to calculate each store’s conditional paid count. In this synthetic fixture, east contains paid, pending, paid orders, so its paid count is 2; west contains only a pending order, so its paid count is 0. SQLite documents aggregate FILTER syntax in its aggregate-functions reference, while its SELECT documentation explains grouping.

The naive query is intentionally incorrect. Its scalar subquery counts every paid order in orders without referring to the outer store group. Consequently, it returns the global paid total, 2, for both east and west. The printed west result makes the failure concrete: it claims two paid orders for a group that has none.

The corrected query keeps COUNT(*) and COUNT(*) FILTER (WHERE state = 'paid') in the same grouped SELECT. Each aggregate is evaluated for that store’s rows, producing east: total=3, paid=2 and west: total=1, paid=0. The assertions establish these results only for the local fixture; they do not validate joins, an order-state taxonomy, or a production reporting query. Aggregate FILTER requires SQLite 3.30.0 or newer. Python’s sqlite3 module uses the SQLite library available in that Python build, so check sqlite3.sqlite_version when supporting older deployments. The f-strings require Python 3.6+.

AI assistance disclosure: AI assisted drafting this local synthetic example.

import sqlite3

connection = sqlite3.connect(":memory:")
connection.execute("CREATE TABLE orders (store TEXT, state TEXT)")
seed_rows = [
    ("east", "paid"),
    ("east", "pending"),
    ("east", "paid"),
    ("west", "pending"),
]
connection.executemany("INSERT INTO orders(store, state) VALUES (?, ?)", seed_rows)

naive = connection.execute(
    """
    SELECT store, COUNT(*) AS total,
           (SELECT COUNT(*) FROM orders WHERE state = 'paid') AS paid
    FROM orders
    GROUP BY store
    ORDER BY store
    """
).fetchall()
corrected = connection.execute(
    """
    SELECT store, COUNT(*) AS total,
           COUNT(*) FILTER (WHERE state = 'paid') AS paid
    FROM orders
    GROUP BY store
    ORDER BY store
    """
).fetchall()

assert naive == [("east", 3, 2), ("west", 1, 2)]
assert corrected == [("east", 3, 2), ("west", 1, 0)]
print("schema: orders(store TEXT, state TEXT)")
print(f"seed rows: {seed_rows}")
print(f"naive global-subquery result: {naive}")
print(f"corrected FILTER result: {corrected}")

schema: orders(store TEXT, state TEXT)
seed rows: [('east', 'paid'), ('east', 'pending'), ('east', 'paid'), ('west', 'pending')]
naive global-subquery result: [('east', 3, 2), ('west', 1, 2)]
corrected FILTER result: [('east', 3, 2), ('west', 1, 0)]