Should you replace NULL with zero in a dashboard?

Only when the business definition supports it. Zero can mean a known amount of nothing. NULL can represent an unknown amount. Replacing every missing amount with zero may make a chart look complete while hiding a data-quality problem. Show coverage alongside the result before choosing how to display missing values.

For the aggregates used here, COUNT(*) counts rows, while COUNT(amount_usd) counts non-NULL amounts. SUM adds known values and returns NULL when none are available. See the SQLite aggregate reference and PostgreSQL aggregate documentation. The example below was executed with SQLite; it is not a test of every database engine.

Five invoices, three different reporting states

Imagine a fictional agency consolidating invoices from US, UK and German operations. All amounts in this exercise are already expressed in USD. Currency conversion is outside the example. Each row represents one invoice, identifiers are unique and the row inventory is assumed complete. A NULL amount means that invoice’s amount has not arrived.

The US has one $100 invoice and one unknown amount. The UK has two known zero-dollar invoices. Germany has one invoice whose amount is unknown. Those are different states, even if a dashboard could display all missing cells as zero.

WITH invoices(region, invoice_id, amount_usd) AS (
  VALUES ('US', 1, 100), ('US', 2, NULL),
         ('UK', 3, 0), ('UK', 4, 0),
         ('DE', 5, NULL)
)
SELECT region,
       COUNT(*) AS invoice_rows,
       COUNT(amount_usd) AS known_amounts,
       SUM(amount_usd) AS known_sum_usd,
       CASE WHEN COUNT(amount_usd) = COUNT(*)
            THEN SUM(amount_usd)
            ELSE NULL END AS complete_total_usd
FROM invoices
GROUP BY region
ORDER BY region;

Expected result

The US known sum is a partial sum, not its complete invoice total. Germany has no known amount to add. The UK total is genuinely zero under this fixture’s assumptions. The CASE expression publishes a complete total only when every represented invoice has a known amount.

Why COALESCE alone does not solve the problem

If the presentation layer replaces Germany’s NULL with zero, viewers cannot distinguish “amount unavailable” from the UK’s measured zero. The US is more subtle: its partial sum is already $100, so replacing NULL aggregate results does nothing to reveal the missing second amount.

Use an explicit label such as “$100 from 1 of 2 invoices; total incomplete.” Do not label $100 a minimum unless your data contract rules out negative amounts. Credit notes or adjustments could reduce the eventual total.

What if a region has no rows?

A region absent from this input will not appear as a group. The completeness check cannot detect an invoice that is entirely missing, and a complete-looking result is not proof of a complete source feed. Reconcile against an expected invoice inventory or a documented ingestion-completion signal.

An aggregate without grouping over an empty input can return a count of zero and a NULL sum. Decide whether that means no activity, an unavailable feed or an invalid filter using evidence outside the aggregate itself. Do not invent missing invoice rows to make the report balance.

How to explain this in an interview

“I would separate recorded invoices, known amounts and the complete total. In the US example, the $100 sum excludes an unknown amount, so I would report it as partial. I would also reconcile invoice coverage because the SQL can only assess rows that reached the dataset.”

Then describe a concrete next step: identify the missing amount, check the source extract and ask the owner whether a delayed update is expected. Keep the reporting period and filters fixed while investigating. For cross-region reports, document conversion rules before adding currencies, rather than assuming a common column name guarantees a common unit.

Checks to add before release

• A fully known group containing zeros should return zero.
• A group with some unknown amounts should retain its known sum but flag the complete total as unavailable.
• A group with all unknown amounts should not look like measured zero.
• A missing group should be assessed against an expected inventory, not silently treated as no activity.

Continue with order and payment reconciliation, or review GROUP BY and aggregate foundations. For reports that need a clear data-quality display, see our dashboard and analytics services.

Synthetic example checked October 2, 2026. The three expected regional rows and empty-input behavior passed local SQLite assertions. No live customer data was used.