Count rows correctly with COUNT star

By Batu · English technical notes

Use COUNT(*) when the question is how many rows are in a group or result set. In this example, the task table has three rows, but one owner value is NULL. COUNT(*) therefore returns 3, while COUNT(owner) returns 2 because that form counts only non-NULL values of its expression. Displaying both figures makes the distinction concrete rather than relying on a table with no missing values.

The assertion checks the aggregate results from this known in-memory fixture. It does not establish that a real application's filter or join is correct: predicates, joins, and grouping determine which rows reach an aggregate. Add an explicit WHERE clause when the business rule calls for a subset, and choose COUNT(column) only when excluding NULL values is intentional.

This code uses standard-library sqlite3 and requires Python 3.6 or later because its output uses an f-string; it uses no newer sqlite3 API. SQLite's aggregate-function reference defines count(X) as the number of non-NULL X values and count(*) as the total number of rows in the group. fetchone() returns the single aggregate result row.

AI-assistance disclosure: this article was drafted with AI assistance and should be adapted to an application's error-handling requirements.

Sources: SQLite built-in aggregate functions, Python sqlite3 Cursor.fetchone, and Python formatted string literals.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE task(id INTEGER PRIMARY KEY, owner TEXT)")
con.executemany(
    "INSERT INTO task(owner) VALUES (?)",
    [("Mina",), (None,), ("Ravi",)],
)

total_rows, owners_present = con.execute(
    "SELECT COUNT(*), COUNT(owner) FROM task"
).fetchone()
assert (total_rows, owners_present) == (3, 2)
print(f"all rows: {total_rows}; non-NULL owners: {owners_present}")

con.close()

all rows: 3; non-NULL owners: 2