Why doesn't SUM just return 0 when there's nothing to add?

SUM over zero rows means 'there was nothing to sum,' and SQL represents 'nothing/unknown' as NULL rather than assuming you meant zero. It's a deliberate distinction — NULL means 'no data,' 0 means 'data that adds up to nothing' — even though for a revenue report the practical difference often doesn't matter.

Which aggregates does this affect?

SUM, AVG, MAX, and MIN over zero rows all return NULL. COUNT is the outlier — COUNT(*) and COUNT(column) both correctly return 0 for zero matching rows, which is part of why this trips people up: one aggregate behaves the 'obvious' way and the others don't.

How does a single NULL total turn a whole report blank?

Any arithmetic expression involving NULL evaluates to NULL — NULL + 5 is NULL, not 5. If a report computes something like revenue minus refunds, and refunds comes back NULL because there were no refunds, the resulting 'net revenue' is NULL too, even though the real answer was just 'revenue, unchanged.'

Wrap the aggregate in COALESCE(SUM(amount), 0) so a NULL result becomes an explicit 0 before it's used anywhere else — in a comparison, in further arithmetic, or in what gets shown to a user.

Is this the same issue as NULL rows getting filtered out of a WHERE clause?

Related but distinct. That's about rows disappearing from a filtered result. This is about the aggregate function's own return value when it has nothing to aggregate — it happens even with a perfectly correct WHERE clause, on a group that legitimately has zero matching rows.

SQL SUM Returns NULL Not Zero | AI Database Bug

You ask for revenue by plan this month. Every plan shows a number except one — the newest plan, the one with zero sales so far — which shows up blank, or NULL, or breaks a later calculation that was supposed to combine it with something else.

Nothing is broken. SUM over zero rows returns NULL, not 0, and that's true even though the intuitively "correct" answer for "total revenue from zero sales" is obviously zero.

The aggregate functions that do this

SUM, AVG, MAX, and MIN all return NULL when there are no rows to aggregate over. This is easiest to see with GROUP BY, where some groups have data and others legitimately don't:

SELECT plan, SUM(amount) AS revenue
FROM orders
GROUP BY plan

If a plan value has zero matching orders rows in whatever period you filtered to, that row's revenue comes back NULL — SQL's way of saying "there was nothing here to sum," which is a subtly different claim than "the sum of nothing is zero," even though for a report they usually mean the same thing to a human reading it.

COUNT is the exception, and the reason this trips people up specifically: COUNT(*) and COUNT(column) both correctly return 0 for zero matching rows. So in the same report, a count column reads 0 right next to a sum column reading blank — same underlying situation (zero rows), different, inconsistent-looking output.

Why one blank cell can break an entire report

The real damage isn't the single blank total — it's what happens when that NULL feeds into anything else. Any arithmetic involving NULL produces NULL: NULL + 5 is NULL, not 5. NULL - NULL is NULL. If a report computes "net revenue = gross revenue − refunds," and a plan had zero refunds this month (a perfectly good outcome), refunds comes back NULL, and net revenue becomes NULL too — even though the real answer was simply "net revenue equals gross revenue, since nothing was refunded."

In a chat answer this often just reads as a confusing blank. In a query feeding a scheduled report or a dashboard number, it can make an entire row or metric silently disappear, which is worse precisely because nothing throws an error to flag it.

The fix: wrap it in COALESCE

COALESCE returns the first non-NULL value from a list of arguments — used here to say "if this comes back NULL, treat it as zero instead":

SELECT plan, COALESCE(SUM(amount), 0) AS revenue
FROM orders
GROUP BY plan

Apply the same pattern anywhere a NULL aggregate might flow into further arithmetic — not just at the final display step, but at each point a NULL could get multiplied through:

SELECT
  COALESCE(SUM(amount), 0) - COALESCE(SUM(refund_amount), 0) AS net_revenue
FROM orders

The prompt that heads this off

When computing SUM, AVG, MAX, or MIN — especially with GROUP BY or for
a specific filtered slice — wrap the aggregate in COALESCE(..., 0) so
a group with zero matching rows returns 0 instead of NULL. This matters
most where the result feeds into further arithmetic, since NULL
propagates through any calculation it touches.

The short version

Zero rows to sum and "the sum is zero" feel like the same fact, but SQL's aggregates draw a real distinction between them — and that distinction is invisible until it silently zeroes out (or blanks out) a downstream number that had nothing wrong with its logic, only a NULL it never expected to carry.

Read-only MCP gateway: mcpserver.design.

Related reading: