Data Analysis / Intermediate: Relationships and Uncertainty / Lesson 7

SQL Query Logic and Honest Denominators

Translate an analytical definition into row filters, grouped summaries, missing-value rules, and a reproducible result order.

About 5 min readContent reviewed 2026-10-25

On this page

Lesson goals

Distinguish WHERE from HAVING.
Explain NULL and the denominators behind COUNT and AVG.
Specify deterministic ORDER BY and typed input.

Define the population before the query

Start with a sentence such as “total known amounts of open orders by region.” State whether a row represents an order or a line, which statuses qualify, and how an unknown amount is handled. SELECT describes output, FROM identifies input, and WHERE retains individual rows whose predicate is true. SQL is a declaration of the required relation; its written order is not a physical execution schedule.

Record the count before and after filtering. A query that runs successfully can still answer a different population question. In SQLite, permissive storage typing does not replace upstream validation: malformed numeric text can produce surprising aggregate behavior. These examples load synthetic typed values into a private :memory: connection and close it explicitly; no configured database is used.

Separate eligible rows from eligible groups

WHERE determines which rows contribute to aggregation; HAVING determines which completed groups remain. To find regions with at least fifty in open-order value, first select open rows, group by region, then test the sum. Filtering individual amounts above fifty would discard the twenty and forty that legitimately combine to sixty. Logical stages explain this difference even when an optimizer rearranges safe operations.

The program retains North, totaling sixty, and rejects South, totaling twenty-five. Its second result reports four open rows but only three known amounts. Bound parameters keep filter values separate from SQL structure. The transaction context of sqlite3 is not itself a connection closer, so contextlib.closing owns the resource lifecycle here.

python Filter rows, then groups

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as db:
    db.execute("CREATE TABLE orders(region TEXT, status TEXT, amount REAL)")
    db.executemany("INSERT INTO orders VALUES (?, ?, ?)", [
        ("North", "open", 20.0), ("North", "open", 40.0),
        ("North", "closed", 200.0), ("South", "open", 25.0),
        ("South", "open", None),
    ])
    query = """SELECT region, COUNT(*), COUNT(amount), SUM(amount)
               FROM orders WHERE status = ? GROUP BY region
               HAVING SUM(amount) >= ?
               ORDER BY SUM(amount) DESC, region ASC"""
    print(db.execute(query, ("open", 50)).fetchall())
    print(db.execute("SELECT COUNT(*), COUNT(amount) FROM orders WHERE status = ?",
                     ("open",)).fetchone())

Expected output

[('North', 2, 2, 60.0)]
(4, 3)

North combines 20 and 40. The closed 200 never contributes. South has two open rows but only one known amount.

Make unknown values visible in the denominator

NULL represents missing information, not zero or an empty string. Comparisons such as amount = NULL do not locate it; use IS NULL. Ordinary comparisons involving NULL yield unknown, and WHERE does not retain unknown results. COUNT(*) counts rows, COUNT(amount) counts non-NULL amounts, and AVG(amount) uses known amounts rather than treating absence as zero.

For the open orders above, the known-value average is eighty-five divided by three, not by four. Report both coverage and the chosen denominator when missingness could matter. COALESCE can create a display default, but placing it inside AVG changes the metric. NOT IN also needs care when its comparison set contains NULL; a correlated NOT EXISTS often expresses absence more clearly.

Make row selection and presentation reproducible

Rows have no promised presentation order without ORDER BY. LIMIT selects a quantity, not automatically the newest or largest records. Add a unique tiebreaker when a fixed-size selection must be stable. The second program chooses the two largest amounts and breaks their tie by identifier; an index or an earlier display order is not a substitute for that contract.

The same data shows that amount <> 0 retains identifiers three and four but excludes both zero and NULL. IS NULL selects identifier one explicitly. Each result is ordered, so the expected lists are meaningful. If the business wants every tied boundary value, define that rule separately rather than assuming a fixed LIMIT preserves all ties.

python Unknown, zero, and tied selection

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as db:
    db.execute("CREATE TABLE readings(id INTEGER PRIMARY KEY, amount REAL)")
    db.executemany("INSERT INTO readings VALUES (?, ?)",
                   [(1, None), (2, 0.0), (3, 10.0), (4, 10.0)])
    def ids(query):
        return [row[0] for row in db.execute(query)]
    print(ids("SELECT id FROM readings WHERE amount <> 0 ORDER BY id"))
    print(ids("SELECT id FROM readings WHERE amount IS NULL ORDER BY id"))
    print(ids("SELECT id FROM readings ORDER BY amount DESC, id ASC LIMIT 2"))

Expected output

[3, 4]
[1]
[3, 4]

NULL fails the ordinary inequality test, while the explicit null test finds it. The final ordering selects ids3 and4 deterministically.

Audit meaning before optimizing

List the intended output grain, filters, grouping keys, aggregate definitions, and missing-value policy beside the query. SQLite allows some bare-column aggregate queries that stricter engines reject; do not interpret an arbitrary ungrouped label as a trustworthy group description. Include only justified grouping expressions and aggregates, and treat portability as an explicit requirement.

Validate tiny cases containing no eligible rows, NULL, zero, ties, and duplicate source records. Reconcile aggregate totals with eligible base rows before adding indexes or discussing speed. No pandas engine or external server is required for these examples. A correct SQL analysis states which records represent the population and why each reported numerator and denominator belongs together.

Common errors and fixes

misuse of aggregate: SUM()

Why it happens: A group-level test was placed in WHERE.
How to fix it: Filter source rows with WHERE and completed sums with HAVING.
Prevent it next time: State each predicate's stage before writing SQL.

Unknown amounts were counted as measured zeros.

Why it happens: A replacement inside the aggregate changed its denominator.
How to fix it: Report known-value count and an explicit missing-data policy.
Prevent it next time: Test NULL independently from zero.

The first two rows changed between runs.

Why it happens: LIMIT was used without a complete ordering contract.
How to fix it: Add business ordering and a unique tiebreaker.
Prevent it next time: Include equal-ranked records in query tests.

Check your understanding

1. Four eligible rows have amounts20,40,25,NULL. Give COUNT(*), COUNT(amount), and AVG(amount).

Show worked solution

They are4,3,and85/3, approximately28.333. The NULL row contributes to the event count but not the measured-amount average. Replacing NULL with zero would instead divide85 by4 and must be described as another metric, not a harmless cleanup.

2. A region has open orders30 and40 and a closed order200. Select regions whose open total exceeds50.