∑Aggregate functions
Fold many rows into summaries
Aggregates (COUNT, SUM, AVG, MIN, MAX, …) collapse a multiset of rows into one value per group when you use GROUP BY, or into a single row for the whole query when you omit grouping. Everything downstream, dashboards, invoicing, QC release gates, assumes you already fixed the grain (what one row means) before the fold.
Whole-table aggregates
SELECT COUNT(*) AS rows_total,
COUNT(DISTINCT patient_id) AS patients,
AVG(length_bp)::numeric(10,2) AS avg_len
FROM sequencing_reads;| rows_total | patients | avg_len |
|---|---|---|
| 125000000 | 8840 | 148.62 |
Join first, aggregate second, without checking duplicates
A one-to-many join multiplies rows; SUM(amount) and COUNT(*) both inflate. DISTINCT is not a substitute for understanding the model.
Aggregate at the correct grain, then join
Pre-aggregate in a subquery or CTE so each key appears once, or join only to dimension tables with guaranteed 1:1 keys before folding money or counts.
📋NULLs and COUNT
Same English word “count,” different SQL semantics.
| Aggregate | NULL handling | Empty set |
|---|---|---|
| COUNT(*) | Counts rows, not column values | 0 |
| COUNT(col) | Skips NULL in that column | 0 if no non-NULL values |
| SUM / AVG | Skips NULL inputs | No rows → NULL; AVG ignores NULLs in denominator |
Conditional aggregates
PostgreSQL-style FILTER (WHERE …) keeps several metrics in one pass readable, e.g. failed wells vs total wells, without repeating large joins.
TL;DR
Aggregate functions (COUNT, SUM, AVG, MIN, MAX) collapse many rows into one value per GROUP BY group (or one row for the whole query). COUNT(*) counts rows; COUNT(col) ignores NULL in that column; SUM/AVG ignore NULL inputs. Join before aggregating only at the grain you mean, one-to-many joins duplicate rows and inflate sums unless you de-duplicate or subquery first.