COUNT counts rows, not things

A lesson from Successful Pipelines, Wrong Numbers. Play it above, or read it through below.

• One order matched two payments. The join emitted a row for each.

  The common read: A join attaches the matching row to the row I started with, so the result still has one row per thing I was counting.

  The order table holds one row for order 1001, and the join to payments returns two, because the cafe paid that invoice in two installments. The documentation defines an inner join as producing, for each row of the left input, a row for each row of the right input that satisfies the condition. Both rows carry the same order_id, the same cafe and the same 640.00, differ only in the payment columns, and nothing in the output marks either one as a defect.

  The word for how many rows a table holds per real-world thing is grain, this course's word rather than PostgreSQL's: orders is at one row per order, payments at one row per payment, and the joined result at one row per payment that matched an order. Nobody wrote that last grain down. The tile has used this join since the dashboard was built, and the two people who reviewed its query both read the join and neither checked the grain.

• The aggregate does not read your tables. It reads the table the FROM clause built.

  The common read: The aggregate reads my tables, so count(*) counts orders and sum(order_total) adds each order's total once.

  The clauses after FROM never operate on orders itself. The documentation says the result of the FROM list is an intermediate virtual table, and WHERE, GROUP BY and HAVING transform that table rather than yours. You know count(*) counts input rows; the new part is that those rows belong to no table in the schema, and a parent's column appears on one row per child. So sum(o.order_total) adds the order's 640.00 twice, and sum(p.amount) adds each payment once.

  Fan-out is this course's word for what the join did: the join outputs a parent row once per matching child, so one parent row becomes several, and the multiplier comes from the child's data rather than from either table's declaration. With four orders and three payments, the join returns three rows: the number of payments that matched an order, which is not a number anyone asked for. Two orders have received a payment, the tile says three, and the tile will keep reporting the payment count.

• COUNT(DISTINCT) fixes only the column it is given. The row set does not change.

  The common read: The fan-out was a duplicate-row problem, so counting the distinct order ids removes it and the query is correct again.

  DISTINCT inside an aggregate deletes no rows. The documentation defines the form as the number of distinct non-null values of the column named, a statement about the values one aggregate reads, not about the rows those values are in, so every other expression in the SELECT still reads all four joined rows. You know from the previous lesson that a distinct count cannot report on rows it discarded. By the same rule, it cannot report how many rows carried a value it counted once.

  After the third payment, the row count goes from 3 to 4, the billed total from 1530.00 to 2170.00, the collected total from 730.00 to 890.00 because a payment was recorded, and the distinct count stays at 2. The collected total is correct only because payments contributed one row per payment. The billed total should read 890.00. An audit of the tile closes with no finding, and the billed figure moves further from the ledger with every installment.

• The multiplier was 1 because of the data, not because of the schema

  The common read: This join returns one row per order in every environment I have tested, so it is a one-to-one join.

  Look in the roastery's payments table definition for a constraint that forbids a second payment on one order: there is none. payment_id is the primary key and order_id is a foreign key with no unique constraint, so two rows sharing an order_id were always legal. The tile was correct for a year only because nobody paid in installments. When wholesale customers asked to, the team added an insert path without changing the schema, and the ticket for the wrong tile was filed against the payments importer.

  The same holds for any join on a key with no unique constraint. Code review misses it because a multiplier of 1 changes no count, sum or average, in development, in staging, or in a fixture written the same week as the query. The multiplier depends on the data, and nothing in the query text shows it. Per-order match counts here are 3, 1, 0 and 0; the day before payment 9002 was recorded they were 1, 1, 0 and 0.

• Two child tables of one parent multiply each other's row counts

  The common read: The second join reads a different table, so it adds columns to the rows I already have.

  The second join does not operate on orders. It operates on the table the first join produced, and applies the definition to that table: for each row of the left input, one row for each matching row of the right input. Three payments and two credits on one order therefore produce six rows, each payment repeated once per credit and each credit once per payment, and each of the two sums in that SELECT is multiplied by the other table's row count, not by its own.

  In the extreme case there is no join predicate at all. A cross join, or a comma-separated FROM list, pairs every row of one input with every row of the other, and the documentation gives the result as N times M rows. The roastery's four orders and four payments make sixteen rows that way, every row valid and none flagged, and SELECT count(*) returns a number close to a normal week's orders.

  With inputs this small, an accidental cross join returns a plausible number rather than an obviously wrong one; on large tables the product is usually too big to miss, unless a filter has cut both sides down to a few rows first. The reconciliation report that joined payments and credits showed one cafe both over-paid and over-credited, and the ticket asked whether the credits importer was writing every row twice.

• LEFT JOIN guarantees a minimum row count. It does not guarantee a maximum.

  The common read: A LEFT JOIN preserves the left side, so the result has one row per left row and joining more tables cannot lose or duplicate anything.

  Name the number of rows a LEFT JOIN returns over four orders when one of those orders was paid three times.

  Switch that inner join to a LEFT JOIN so no order is lost, and the row count

  A LEFT JOIN guarantees a minimum row count, never a maximum. The documentation states the minimum: an inner join is performed, then for each left row with no match a joined row is added with nulls in the right side's columns, so the joined table always has at least one row for each left row. All four orders appear: the two unpaid ones with null payment columns, and the one paid three times on three rows.

  Switching an inner join to an outer one repairs row loss, and row loss and row multiplication are different failures with different fixes. Fixing row loss does nothing about multiplication and usually makes the arithmetic worse, because the count now includes rows that had nothing to count. The one-word edit was approved in code review on the reasoning that no order should be excluded, and the tile's number went up rather than down.

• Ask what the question is. Existence is a predicate and a total is an aggregate at the source grain.

  The common read: A join is how you reach another table, so any question about a related table is answered by joining to it.

  Decide which question the tile asks before writing the query. How many orders have received a payment is an existence question, and an existence question is a predicate, not a row source. EXISTS evaluates a subquery to determine whether it returns any rows, and the result depends only on whether rows come back, not on their contents. It answers true or false once per outer row, so it can remove an outer row but never repeat one: three payments against one order make it true once.

  You saw NOT EXISTS last lesson as the replacement for NOT IN; the positive form is called a semi-join, a query pattern rather than a SQL keyword. How much has been collected is a total, computed where rows are one per thing being added up. Group by the parent's key and aggregate only the child's columns, as the documentation's total-sales-per-product example does, or aggregate the child to one row per parent key in a subquery and join that.

  In the figure the payments are reduced to one row per order before the join, so each parent column is read once per parent: each paying order's total is added once, and both numbers are 890.00, which matches the ledger.

• Every number in this lesson was a correct answer to the query someone wrote

  The common read: These numbers were wrong, so somewhere in the pipeline there is a bug to find.

  Name what would have to be fixed to make the paid-orders tile report 2 instead of 4. There is no bug anywhere in this lesson. count(*) returned the number of rows in the table the FROM clause built, and three ways of writing one question returned 4, 2 and 2 on the same data. All three are correct; only the two that return 2 match the dashboard's label. Before anyone repaired it, the tile had counted payments without error since it was deployed, under a label that says orders.

  The cause was the match count, which comes from the data and changes no result while it is 1. So list the joins that no constraint keeps at one row per parent, not the queries that look wrong: check each child table for a unique constraint on the parent key, because today's per-order counts can all be 1, as they were here for a year. Then list the reports that aggregate a parent column after joining a table that has none.