COUNT(col) changed what a ratio meant

A report showed the share of orders with a shipping date as COUNT(shipped_at) * 1.0 / COUNT(*). It was right for a while. Then pending orders started arriving in the same table with shipped_at NULL, and the share dropped. Nothing in the query changed.

COUNT(*) counts input rows. COUNT(shipped_at) counts rows where that value is not null. The ratio was always "non-null dates among all rows", and the set of rows moved under it.

Three things to check, and they are not equivalent.

1. Name the denominator. COUNT(*) FILTER (WHERE status <> 'pending') says which rows the share is about.
2. Watch the division. In PostgreSQL count returns bigint, so COUNT(a) / COUNT(*) truncates to zero for any share below one. Hence the * 1.0.
3. Ask what NULL means in that column. Not shipped yet and shipping date unknown are different facts, and a count can't tell them apart.

With two shipped orders and two pending ones the versions disagree in plain sight.

SELECT COUNT(shipped_at) * 1.0 / COUNT(*) AS naive,
       COUNT(shipped_at) * 1.0
         / COUNT(*) FILTER (WHERE status <> 'pending')
         AS shipped_share
FROM orders;
-- naive 0.5, shipped_share 1.0

R has this split too. length(x) counts the NA entries, sum(!is.na(x)) doesn't. When the report is built in R I print sum(is.na(x)) beside the ratio, so the reader sees how many rows the numerator skipped.