COUNT counts rows, not things

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

• One order arrived as two rows. The join emitted both of them.

  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.

  Count the rows the roastery's own order table holds for order 1001, then count the rows a join hands back for the same order. Not the same number. The order table holds one row for it, and the join to payments hands back two, because the cafe paid that invoice in two installments and the documentation defines an inner join to produce, for each row of the left input, a row for each row of the right input that satisfies the condition. Two matches, two rows.

  Nothing about that is a defect and nothing in the output marks it. Both rows carry the same order_id, the same cafe and the same 640.00, and differ only in the payment columns. 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 found an order. Nobody wrote that last grain down anywhere.

  The tile on the roastery's ops dashboard is labelled orders paid this week and it has been running on this join since the dashboard was built. It is filed under reporting, and it was reviewed by two people who both read the join and neither read 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.

  No clause in a SELECT ever sees orders. The documentation is explicit: the result of the FROM list is an intermediate virtual table, and WHERE, GROUP BY and HAVING transform that table rather than yours. So count() counts its rows and sum(o.order_total) adds one addend per row: the order's own 640.00 twice, every payment once. You hold that count() counts input rows; what is new is that those rows are not rows of anything named in the schema, and a parent's column sits on one per child.

  Fan-out is this course's word for what the join did: a parent row leaves the join as more rows than it entered with, one per matching child, and the multiplier lives in the child's data rather than in either table's declaration. Four orders, three payments, and the join returns three rows: the count of payments that found an order, not of anything a person would ask about. Two cafes have paid. The tile says three, and it will keep tracking the payment count exactly.

• COUNT(DISTINCT) repairs the column you pointed it at. The row set is untouched.

  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 what one aggregate sees, not about the rows it sees them in, so the four joined rows are still four joined rows for every other expression in the SELECT. You already hold the half that matters: a distinct count cannot report on rows it discarded. Turn that around: it cannot report on rows it collapsed either.

  Watch the third payment hit a SELECT with both forms. The row count moves from 3 to 4, the billed total from 1530.00 to 2170.00, the collected total from 730.00 to 890.00 because a real payment really arrived, and the distinct count sits at 2. The distinct count is right. The collected total is right because payments contributed one row per payment, for no other reason. The billed total reads 2170.00 against an honest 890.00. The repair made one column correct and immobile, not the query.

  This is the state that survives an audit. Somebody asked whether the paid-orders tile was double counting, somebody proved it was not, the finding was closed, and the billed figure on the same dashboard drifts further from the ledger with every installment anyone pays.

• 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.

  Read the roastery's payments table 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 nothing unique about it, so two rows sharing an order_id were always legal, and the tile was correct for a year for one reason only: nobody had paid an invoice in installments yet.

  That is the general case, and why review misses it. Fan-out with a multiplier of 1 is arithmetically invisible: every count, sum and average comes back as though the join were one to one, in development, in staging, in the fixture somebody wrote the same week as the query. The multiplier lives in the data and the query cannot see it. Per-order match counts here are 3, 1, 0 and 0; the day before payment 9002 landed they were 1, 1, 0 and 0.

  The migration that made the second row appear was a feature, not a defect: wholesale customers asked to pay in two parts, and the change was one insert path with no schema change at all. The paid-orders tile drifted the same week, and the ticket was raised against the payments importer that had just shipped.

• Two children of one parent multiply each other

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

  The second join never sees orders. It sees whatever the first join produced, and it applies the definition to that: 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 the two sums on that line are wrong by each other's counts rather than by their own.

  The extreme case removes the predicate entirely. 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 arithmetic as N \ M. The roastery's four orders and four payments make sixteen rows that way, every one valid, none flagged, and a SELECT count() over them returns a number in the range you would expect a week's orders to be in. An accidental cross join produces nothing absurd. It produces something plausible.

  The reconciliation report that pulled payments and credits together showed one cafe both over-paid and over-credited, and the ticket asked whether the credits importer was writing every row twice.

• LEFT JOIN promises a minimum. It never promised 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 hands back 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 floor, never a ceiling. The documentation says 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, thus the joined table always has at least one row for each row in T1. At least. The four orders come back, the two nobody has paid with null payment columns, and the one paid three times comes back three times: six rows from four orders, fan-out intact.

  Switching an inner join to an outer one is a repair for row loss, and row loss and row multiplication are two different failures with two different fixes. Doing the first does nothing about the second, and it usually makes the arithmetic worse, because the count now includes the rows that had nothing to count. The dashboard change that caused this was one word in a code review, approved on the reasoning that no order should be excluded, and the tile 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 of two questions the tile is asking before deciding how to write it, because the two have different shapes and only one of them involves adding rows to anything. How many cafes have paid something is an existence question. How much have they paid is a total.

  An existence question is a predicate, not a row source. EXISTS takes a subquery, evaluated, the documentation says, to determine whether it returns any rows; the result depends only on whether any rows are returned, not on their contents, so it answers true or false per outer row and cannot change the outer row count. Three payments against one order make the predicate true once. You met NOT EXISTS last lesson as the NOT IN repair. The positive form is a semi-join: a shape, not a keyword.

  A total is an aggregate, computed where rows are one per thing being added up. The documentation's worked example is this: total sales per product, aggregated over the joined result with GROUP BY at the product's grain. Group at the child's grain, or aggregate the child in a subquery and join one row to the parent, so the parent's columns are read once each. The payments collapse to one row per order, the order total is added once, and both numbers are right for the first time.

• 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, every time, and three spellings of one question returned 4, 2 and 2 on the same data. All three are correct, and only one answers the dashboard's label. The most accurate instrument on that dashboard is the paid-orders tile, which has counted payments perfectly since the day it shipped.

  The mechanism was never in the query text; it was in the match count, which lives in the data and is invisible at 1. The list worth writing down is not the queries that look wrong. It is the joins whose one-row-per-parent behaviour was never a promise: child tables with no unique constraint on the parent key, and reports that aggregate a parent column downstream of one. Both are findable with the per-order count of child rows this lesson put on screen, never by reading the report.