Count distinct non-null values in a fixture table

By Batu · English technical notes

Also published in our primary archive.

Count distinct non-null values in a fixture table

To count distinct non-null values in SQLite, use COUNT(DISTINCT value). With values A, A, B, and NULL, it returns 2: A and B. COUNT(*) and COUNT(value) answer related but different questions.

The fixture prints its schema and the four seed rows before running all three aggregates in one query. COUNT(*) counts table rows and returns 4. COUNT(value) excludes the null but retains duplicates, so it returns 3. Adding DISTINCT deduplicates the non-null values before counting, producing 2. The assertions preserve these separate meanings and make the intended aggregate explicit.

SQLite’s aggregate-function documentation defines count(X) as the number of times X is not null and count(*) as the number of rows. The DISTINCT modifier applies to the aggregate argument, which is why the duplicate A contributes once to COUNT(DISTINCT value). This code uses the standard-library sqlite3 module and no newer Python version requirement applies.

Do not infer a count of nulls by subtracting without first choosing a denominator and joins carefully; this isolated table has exactly four inserted rows. Likewise, case sensitivity and collation can affect whether textual values are distinct in a different schema. The assertion proves only the stated fixture and default comparison behavior.

Sources: SQLite SELECT and SQLite aggregate functions.

AI assistance disclosure: This article was drafted with AI assistance and verified against the cited documentation and a synthetic in-memory example.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE sample (value TEXT)")
seed_rows = [("A",), ("A",), ("B",), (None,)]
con.executemany("INSERT INTO sample (value) VALUES (?)", seed_rows)

counts = con.execute(
    "SELECT COUNT(*), COUNT(value), COUNT(DISTINCT value) FROM sample"
).fetchone()

print("schema: CREATE TABLE sample (value TEXT)")
print("seed rows:", seed_rows)
print("COUNT(*), COUNT(value), COUNT(DISTINCT value):", counts)

assert counts == (4, 3, 2)
con.close()

schema: CREATE TABLE sample (value TEXT)
seed rows: [('A',), ('A',), ('B',), (None,)]
COUNT(*), COUNT(value), COUNT(DISTINCT value): (4, 3, 2)