← SQL Foundations for Data Engineering

▶ Play from here · 22 min

Starts this lesson and continues through 2 more to the end of the course.

Counting things correctly

Four counts, four different questions

SELECT
  COUNT(*)                     AS rows_,          -- how many rows
  COUNT(customer_id)           AS non_null,       -- how many have a customer
  COUNT(DISTINCT customer_id)  AS customers,      -- how many distinct customers
  SUM(CASE WHEN paid THEN 1 ELSE 0 END) AS paid_  -- how many satisfy a condition
FROM orders;

These return four different numbers and answer four different questions. Choosing the wrong one is the most common cause of a dashboard that is nearly right.

The documented distinction, verbatim (PostgreSQL: Aggregate Functions, verified 2026-08-12):

• count(*) — "Computes the number of input rows."
• count("any") — "Computes the number of input rows in which the input value is not null."

That's the whole rule. COUNT(*) counts rows; COUNT(expr) counts non-null values of expr.

The empty-set asymmetry

"It should be noted that except for count, these functions return a null value when no rows are selected. In particular, sum of no rows returns null, not zero as one might expect"

So over an empty set:

COUNT is the only aggregate that returns a number over no rows. Everything else returns NULL.

⚠️ This is why COALESCE(SUM(x), 0) is idiomatic and COALESCE(COUNT(x), 0) is pointless. If you see the latter in a codebase, the author didn't know the rule — and probably guessed elsewhere too.

COUNT(*) on a LEFT JOIN is almost always wrong

Restating lesson 2's example because it's the highest-frequency instance of this bug:

-- WRONG: orders with zero items report 1
SELECT o.order_id, COUNT(*) AS items
FROM orders o LEFT JOIN order_items i ON i.order_id = o.order_id
GROUP BY o.order_id;

-- RIGHT: orders with zero items report 0
SELECT o.order_id, COUNT(i.item_id) AS items
FROM orders o LEFT JOIN order_items i ON i.order_id = o.order_id
GROUP BY o.order_id;

Rule: after a LEFT JOIN, count a column from the right-hand table, never *.

The unmatched row still exists — it just has NULLs in it. COUNT(*) counts the row. COUNT(col) counts the value, and there isn't one.

Conditional counting

Two ways, and the difference matters when you mix them.

-- Both count paid orders:
SUM(CASE WHEN paid THEN 1 ELSE 0 END)    -- returns 0 for no rows... no: returns NULL for no rows
COUNT(CASE WHEN paid THEN 1 END)         -- returns 0 for no rows

⚠️ The SUM form returns NULL over an empty set; the COUNT form returns 0. Prefer the COUNT form — it's shorter, it needs no ELSE, and it degrades sensibly.

Note how the COUNT version works: the CASE has no ELSE, so non-matching rows evaluate to NULL, and COUNT skips NULLs. That's the same rule doing useful work rather than causing a bug.

-- The pattern to memorise: conditional counts in one pass
SELECT
  COUNT(*)                                        AS total,
  COUNT(CASE WHEN status = 'paid'     THEN 1 END) AS paid,
  COUNT(CASE WHEN status = 'refunded' THEN 1 END) AS refunded,
  COUNT(DISTINCT CASE WHEN status = 'paid' THEN customer_id END) AS paying_customers
FROM orders;

That last line is worth studying — a conditional COUNT DISTINCT. It answers "how many distinct customers paid us" in a single scan, and it's a common interview follow-up.

COUNT(DISTINCT ...) and what it costs

COUNT(DISTINCT x) is semantically simple and operationally expensive: the engine must track every distinct value it has seen, which is memory proportional to cardinality, and it generally can't be computed from partial aggregates the way COUNT(*) can.

Three practical consequences:

1. Multiple COUNT(DISTINCT) in one query can be much more expensive than one. If a query has five of them and is slow, that's your first suspect.
2. COUNT(DISTINCT a, b) across multiple columns is not universally supported — I have not verified which of Redshift, Athena/Trino, and Spark SQL support the multi-column form, so check your engine. The portable workaround is to concatenate with a separator that cannot appear in the data, or count distinct over a struct/row where supported.
3. Approximate counting exists for when exactness isn't required — typically an APPROX_COUNT_DISTINCT-style function backed by HyperLogLog. Availability and function name vary by engine; check yours rather than assuming. For a dashboard tile showing "active users: 4.2M", approximate is almost always the right trade.

⚠️ Do not swap in an approximate count without telling whoever reads the number. "Roughly 4.2 million" and "4,213,887" are different claims, and someone will reconcile the second one against another system.

GROUP BY, HAVING, and WHERE

The order of operations decides what you can reference:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

• WHERE filters rows before grouping. It cannot see aggregates.
• HAVING filters groups after aggregation. It can see aggregates.

-- Filter rows first (cheaper), then filter groups
SELECT customer_id, COUNT(*) AS orders_
FROM orders
WHERE order_date >= DATE '2026-01-01'    -- rows: before grouping
GROUP BY customer_id
HAVING COUNT(*) > 5;                     -- groups: after aggregating

⚠️ Putting a row-level predicate in HAVING is a correctness and performance smell. It works in some engines but filters after the aggregation has already been computed over rows you didn't want. Put row conditions in WHERE.

Because SELECT is evaluated after GROUP BY and HAVING, a column alias defined in SELECT is not reliably available in WHERE or HAVING — engines differ, and some permit it as an extension. Repeat the expression, or wrap the query in a CTE, rather than depending on the extension.

Counting over a window instead of collapsing

Sometimes you want the count and the rows. Then use a window function rather than GROUP BY:

SELECT
  order_id,
  customer_id,
  amount,
  COUNT(*)  OVER (PARTITION BY customer_id) AS customer_order_count,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

GROUP BY collapses rows. OVER (PARTITION BY ...) keeps them and attaches the aggregate to each. That distinction is the subject of module SQL1, and lesson 4 uses it immediately.

Check yourself

1. SUM(amount) returns NULL but COUNT(*) returns 0 on the same empty filter. Why?
2. After a LEFT JOIN, which count gives 0 for unmatched parents?
3. Write "distinct customers who have a paid order" as a single aggregate expression.
4. A query has five COUNT(DISTINCT ...) and is slow. What's your hypothesis?
5. Why is HAVING order_date >= '2026-01-01' a smell?

Answers

1. COUNT is the only aggregate that returns a value over zero rows; all others return NULL. This is documented explicitly.
2. COUNT(right_table.some_column). COUNT(*) counts the unmatched row itself and returns 1.
3. COUNT(DISTINCT CASE WHEN status = 'paid' THEN customer_id END) — the CASE has no ELSE, so non-paid rows become NULL and are skipped.
4. Distinct counting requires tracking every distinct value, so it's memory-bound in cardinality and resists partial aggregation. Five of them in one query multiply that. Consider approximate counting, or splitting the query.
5. It's a row-level predicate, so it belongs in WHERE. In HAVING it filters after aggregating over rows you never wanted, which is both slower and easy to reason about incorrectly.

Teaching this section

• Open by writing the four counts on the board and asking whether they're equal. Most people say "roughly". Then show a table where all four differ.
• Demo: the LEFT JOIN count bug. Show COUNT(*) returning 1 for an order with no items. It's the fastest way to make the count(*) vs count(expr) rule permanent.
• Good discussion: "Where in our dashboards would an approximate distinct count be fine, and where would it be a problem?" It forces people to articulate which numbers get reconciled against other systems.
• Watch for: COALESCE(COUNT(x), 0). Ask why it's there. It's a reliable tell that someone is pattern-matching rather than reasoning.
• Timing: 20 min presented, 10 discussion.