How Do Aggregates Treat NULL and Empty Sets?

Every aggregate except COUNT(*) ignores NULLs. SUM skips them, AVG skips them in both the numerator and the denominator, MIN and MAX skip them, and COUNT(column) counts only the non-NULL values. The effect is that an aggregate describes the population of values that are actually present, not the population of rows.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2)
);

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, NULL),
  (3, 1, 340.50),
  (4, 2, NULL),
  (5, 3,  75.25);

SELECT COUNT(*) AS rows_in_group,
       COUNT(amount) AS non_null_amounts,
       SUM(amount) AS total,
       AVG(amount) AS average,
       MIN(amount) AS smallest,
       MAX(amount) AS largest
FROM orders;

The results: 5 rows in the table, 3 of them with an amount, a total of 535.75, an average of 178.583333333333 — which is 535.75 divided by the three values that are actually present — a smallest of 75.25 and a largest of 340.50. The two NULL amounts contributed nothing to the sum, nothing to the average's denominator, and nothing to the minimum or maximum.

That is usually what you want, and it is why AVG(amount) is not the same as SUM(amount) / COUNT(*). The second expression divides 535.75 by 5 instead of by 3, producing 107.15 and quietly pretending there were two zero-value orders.

Where the NULL-skipping bites is when a NULL means something specific. If a missing amount means "the order was cancelled and has no value" rather than "we did not record the amount", then including those rows in a count but not in a sum is a defensible choice, but you have to make it deliberately. And if a NULL amount means the data is corrupt, then AVG silently hides the corruption and the report looks healthy.

One last detail: COUNT(*) counts rows, so it counts rows whose every column is NULL. COUNT(amount) counts values. The difference between the two numbers is exactly the number of rows with a missing value in that column, which is often the number the report is trying to surface.

Filtering to an empty set is the cleanest way to see the behaviour, and the results are genuinely inconsistent enough to be worth memorising.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 2, 340.50);

-- A filter that matches nothing produces an empty input set.
SELECT COUNT(*) AS count_empty,
       COUNT(amount) AS count_values_empty,
       SUM(amount) AS sum_empty,
       AVG(amount) AS avg_empty,
       MIN(amount) AS min_empty,
       MAX(amount) AS max_empty
FROM orders
WHERE customer_id = 999;

One row comes back: count_empty is 0, count_values_empty is 0, and sum_empty, avg_empty, min_empty and max_empty are all NULL. So COUNT returns 0 while every other aggregate returns NULL.

The reasoning is that zero is a meaningful count — there are zero matching rows — whereas there is no meaningful sum, average, minimum or maximum of nothing. Returning NULL rather than 0 for SUM is the right call, because a zero total would claim that the value is known to be zero, when in fact it is unknown.

This matters in application code. A report that divides by COUNT will divide by zero and raise an error in SQLite and PostgreSQL, so it needs a guard such as NULLIF(COUNT(*), 0). A report that divides by SUM gets NULL, which usually becomes a null field in JSON and a blank in the UI — less dangerous, but silently wrong-looking.

The same asymmetry appears per group. A customer whose orders all have NULL amounts produces a group with one row, a COUNT(*) of 1, a COUNT(amount) of 0, and NULL for SUM, AVG, MIN and MAX. So the group exists and has a row count, but none of its measures are computable. If your report groups by customer and shows a revenue column, that customer's revenue should read as NULL or as a deliberate zero — decide which, because the default is neither.