Understand what COUNT(column) leaves out
A report uses COUNT(paid_at) as its invoice count. The number is lower than the list because the aggregate excludes NULL values in that column. COUNT(*) counts rows; COUNT(column) counts non-null values.
Name each count according to its meaning
SELECT COUNT(*) AS invoice_count,
COUNT(paid_at) AS invoices_with_paid_timestamp
FROM invoices
WHERE tenant_id = 42;
The sample tenant ID is illustrative. Bind the authenticated tenant in application code. The second count is not automatically “fully paid invoices” unless your data model guarantees that a paid timestamp means full settlement.
Aggregates inherit join behavior
Joining invoice lines multiplies each invoice into several rows. A later COUNT(*) then counts joined rows, not invoices. Choose the intended grain before aggregating: invoice, line, payment, or customer.
A distinct invoice count can address a particular join, but it should not be a reflexive patch for every inflated result. Sometimes pre-aggregating the child table or using an existence condition expresses the query more clearly.
Test invoices with no payment timestamp, multiple lines, and multiple payments. Write the expected result from the fixture manually. If the report combines totals and counts, verify they use compatible filters and grains. An accurate-looking number without a clear definition can mislead an operator just as much as an obvious SQL error.