SQL Aggregate Functions: Counts, NULLs, Empty Sets, and Groups

SQL aggregates: count observations before interpreting results

COUNT, SUM, AVG, MIN, and MAX summarize a set of rows. The critical questions are which rows enter the set, which values are known, and how many output groups remain. This PostgreSQL 16 lesson uses five readings with a repeated value, a real zero, and unknown readings. It is a foundation for multi-stage reporting aggregates, which covers conditional counts and weighting.

Know the row and value denominators

Each fixture row is one reading. Station A has values 10, 10, and NULL; station B has 0 and NULL. NULL means the reading is unknown. Ten appearing twice is two observations with equal values, not necessarily duplicated data. Zero is a known observation and must stay in the ordinary average.

Run the five core aggregates together

WITH readings(id,station,value) AS (
 VALUES(1,'A',10::numeric),(2,'A',10),(3,'A',NULL),(4,'B',0),(5,'B',NULL)
)
SELECT COUNT(*) AS rows,COUNT(value) AS known_values,
 COUNT(DISTINCT value) AS distinct_known_values,SUM(value) AS total,
 ROUND(AVG(value),2) AS average,MIN(value) AS minimum,MAX(value) AS maximum
FROM readings;

rows | known_values | distinct_known_values | total | average | minimum | maximum
5 | 3 | 2 | 20 | 6.67 | 0 | 10

COUNT(*) sees five rows; COUNT(value) sees three non-NULL values. COUNT(DISTINCT value) sees two distinct known values: 0 and 10. SUM is 20 and AVG is 20/3, rounded to 6.67. Removing zero with NULLIF(value, 0) would change the average to 10 and answer a different question. MIN and MAX here use known numeric values; they can also work with supported orderable types such as dates.

Separate empty input from all-NULL input

WITH readings(id,station,value) AS (
 VALUES(1,'A',10::numeric),(2,'A',10),(3,'A',NULL),(4,'B',0),(5,'B',NULL)
)
SELECT COUNT(*) AS rows,COUNT(value) AS known_values,
 SUM(value) AS total,AVG(value) AS average,MIN(value) AS minimum,MAX(value) AS maximum
FROM readings WHERE id<0;

rows | known_values | total | average | minimum | maximum
0 | 0 | NULL | NULL | NULL | NULL

WITH readings(id,station,value) AS (
 VALUES(1,'A',10::numeric),(2,'A',10),(3,'A',NULL),(4,'B',0),(5,'B',NULL)
)
SELECT COUNT(*) AS rows,COUNT(value) AS known_values,SUM(value) AS total
FROM readings WHERE value IS NULL;

rows | known_values | total
2 | 0 | NULL

An aggregate query without GROUP BY still returns one summary row for empty input. These particular numeric aggregates return NULL when no known value exists, while COUNT returns zero. The same NULL total can mean no rows or rows whose values are all unknown; retain counts to distinguish them. Do not generalize this behavior to every aggregate: PostgreSQL array_agg, for example, includes NULL elements.

GROUP BY changes the output grain; HAVING selects groups

WITH readings(id,station,value) AS (
 VALUES(1,'A',10::numeric),(2,'A',10),(3,'A',NULL),(4,'B',0),(5,'B',NULL)
)
SELECT station,COUNT(*) AS readings,COUNT(value) AS known_values,
 SUM(value) AS total,ROUND(AVG(value),2) AS average
FROM readings GROUP BY station ORDER BY station;

station | readings | known_values | total | average
A | 3 | 2 | 20 | 10.00
B | 2 | 1 | 0 | 0.00

The output now has one row per station. A grouped query on empty input has no station groups and therefore returns no rows. An ungrouped aggregate and a grouped aggregate do not have the same empty-result shape. Include each selected dimension in GROUP BY unless a specifically supported functional-dependency rule justifies omitting it.

WITH readings(id,station,value) AS (
 VALUES(1,'A',10::numeric),(2,'A',10),(3,'A',NULL),(4,'B',0),(5,'B',NULL)
)
SELECT station,COUNT(*) AS known_readings,SUM(value) AS total
FROM readings WHERE value IS NOT NULL
GROUP BY station HAVING COUNT(*)>=2 ORDER BY station;

station | known_readings | total
A | 2 | 20

WHERE removes unknown readings before grouping. HAVING then requires at least two surviving readings. The name known_readings describes that changed denominator. A WHERE condition containing AVG(value) at this query level is invalid because row filtering happens before the aggregate result exists.

DISTINCT and defaults are policy choices

WITH readings(id,station,value) AS (
 VALUES(1,'A',10::numeric),(2,'A',10),(3,'A',NULL),(4,'B',0),(5,'B',NULL)
)
SELECT SUM(value) AS observation_total,SUM(DISTINCT value) AS unique_value_total,
 ROUND(AVG(COALESCE(value,0)),2) AS average_with_imputed_zero
FROM readings;

observation_total | unique_value_total | average_with_imputed_zero
20 | 10 | 4.00

SUM(DISTINCT value) totals the set of unique values, not unique reading identities. It discards the second legitimate 10. AVG(COALESCE(value, 0)) invents two zero readings and divides by five, producing 4. Both expressions are valid SQL, but neither is the observed-value average. A default should express a justified business rule; it does not repair missing measurements.

COUNT(email) counts non-NULL emails, not syntactically valid or deliverable emails. MIN(timestamp) finds an earliest known value, not necessarily the complete first event in a partially loaded source. The function’s name does not validate its input data.

Use a short interview explanation

Start with “one row is one reading.” Then explain the five input rows, three known values, and two station groups. Name the policy for NULL and zero before presenting the average. If a join appears before the aggregate, check whether it repeats readings; grouping afterward cannot reverse an already multiplied measure. For that problem, use pre-aggregation at the correct join grain.

Keep correctness separate from execution speed

An aggregate may scan many rows even when it returns one row. An index on the value column is not a promise of a faster total or average. Inspect the plan, selected population, and workload before changing indexes. Prefer exact numeric types when the domain requires exact decimal arithmetic, and validate finite input values if NaN or infinity would violate the domain.

FAQ

Can I average group averages? Only if the intended groups have equal weights or an unweighted group-level mean is the question. Do aggregates require GROUP BY? No; omitting it summarizes one input set. Does COUNT change stored rows? No; these examples are read-only. See aggregate definitions and WHERE, GROUP BY, and HAVING.