Aggregation

Which rows do SQL aggregate functions skip?

By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17

Most of them skip a row when the value they were handed is null. count(*) does not, because it counts rows rather than values, and neither do array_agg, json_agg and jsonb_agg, which keep the null as an element of what they build.

That is why two numbers in one query can disagree about how many rows there are, with no error and no warning. The table below shows ten aggregates over the same three values.

The worked examples read customers, one row per customer, where city is null for some of them. The table is below. What null does inside a comparison or an expression is a separate subject; this page is only about which rows an aggregate leaves out.

You can edit and run two of the queries below in your browser, and there are two exercises at the end.

Schema · ecommerce1 table? = nullable

customers

idinteger

nametext

emailtext

city?text

countrytext

created_attimestamptz

is_activeboolean

SELECT count(*) AS customer_rows,
       count(city) AS rows_with_a_city,
       count(*) - count(city) AS rows_with_no_city
FROM customers;

customer_rows	rows_with_a_city	rows_with_no_city
70	61	9

the customers table in our sandbox: 70 rows, 9 of them with no city

Why do COUNT(*) and COUNT(column) give different numbers?

Because count(*) counts rows and count(city) counts values, and a null is not a value. Eight British customers, one of them with no city:

SELECT count(*) AS count_star,
       count(city) AS count_city,
       string_agg(city, ', ' ORDER BY city) AS string_agg,
       jsonb_agg(city ORDER BY city)::text AS jsonb_agg
FROM customers
WHERE country = 'GB';

count_star	count_city	string_agg	jsonb_agg
8	7	Birmingham, Edinburgh, London, London, London, London, Manchester	["Birmingham", "Edinburgh", "London", "London", "London", "London", "Manchester", null]

four aggregates over the same eight rows. The string lists seven cities and the JSON array holds eight elements

Four answers to "how many" over one group. string_agg dropped the row and says nothing about it. jsonb_agg kept it as an explicit null, so the array length still matches the row count. If a report reconciles against a source and the totals are one apart, this is usually where it happened.

SELECT count(*) AS count_star,
       count(city) AS count_city,
       string_agg(city, ', ' ORDER BY city) AS string_agg,
       jsonb_agg(city ORDER BY city)::text AS jsonb_agg
FROM customers
WHERE country = 'GB';

Which aggregates skip nulls, and which keep them?

Of the ten below, the array and JSON collectors keep them, count(*) counts the row anyway, and the other six skip them. Here they are over a three-row list holding 10, a null, and 20:

SELECT count(*) AS count_star,
       count(x) AS count_x,
       sum(x) AS sum,
       avg(x)::numeric(10,2) AS avg,
       min(x) AS min,
       max(x) AS max,
       string_agg(x::text, ',') AS string_agg,
       array_agg(x)::text AS array_agg,
       json_agg(x)::text AS json_agg,
       jsonb_agg(x)::text AS jsonb_agg
FROM (VALUES (10), (NULL), (20)) AS s(x);

count_star	count_x	sum	avg	min	max	string_agg	array_agg	json_agg	jsonb_agg
3	2	30	15.00	10	20	10,20	{10,NULL,20}	[10, null, 20]	[10, null, 20]

ten aggregates, three input rows, one null among them

count(*) is 3 and every other number behaves as though the null row were not there. The three collectors at the end return all three elements, one of which is null. PostgreSQL's documentation states it for each of them: array_agg collects all the input values, including nulls, and the JSON pair carry the same wording. One narrow exception sits behind that: aggregating a column that is itself an array reaches a different overload, which refuses a null input rather than keeping it.

The JSON pair has strict variants, json_agg_strict and jsonb_agg_strict, which skip nulls instead. They are the exception to the split above: JSON collectors that behave like the six that skip. Those are the ones to reach for when an outer join has put a null into the array.

Does a skipped null change an average?

Yes, and this is the version that reaches a dashboard. avg divides by the number of non-null values, not by the number of rows:

SELECT sum(x) AS sum,
       count(*) AS rows,
       count(x) AS non_null_rows,
       avg(x)::numeric(10,2) AS avg,
       (sum(x)::numeric / count(*))::numeric(10,2) AS sum_over_all_rows
FROM (VALUES (10), (NULL), (20)) AS s(x);

sum	rows	non_null_rows	avg	sum_over_all_rows
30	3	2	15.00	10.00

the same three rows. avg divides 30 by 2, not by 3

Both numbers are defensible and they answer different questions. The average of the values that exist is 15. The average across every row, treating a missing value as zero, is 10. Decide which one the report means. For the second, either divide the sum by count(*) yourself, as the last column above does, or replace the null before the aggregate sees it with avg(coalesce(x, 0)).

How do you count the nulls on purpose?

Two ways, and they agree. Subtract the value count from the row count, or count rows with a FILTER that tests for null:

SELECT count(*) - count(city) AS by_subtraction,
       count(*) FILTER (WHERE city IS NULL) AS by_filter
FROM customers;

by_subtraction	by_filter
9	9

nine customers with no city, counted twice

The FILTER version is worth preferring inside a grouped query, because it restricts one column while the group keeps every row. Moving the same test into a WHERE restricts the whole query at once: it would throw away the 61 customers who do have a city, so every other column in the row would then be describing a different population.

Try it: add GROUP BY country and country to the select list, then run it again. The same two expressions now report per country.

SELECT count(*) - count(city) AS by_subtraction,
       count(*) FILTER (WHERE city IS NULL) AS by_filter
FROM customers;

Practice: counting what is there and what is missing

Aggregate functions are node N013 of our free SQL course, and how null moves through an expression is N063, which is the other half of this subject. No account, nothing to install, nothing to pay.