Learn postgres aggregate functions the correctness-first way: COUNT(*), COUNT(column), COUNT(DISTINCT ...), NULL handling, GROUP BY, HAVING, FILTER, join.
Aggregates look deceptively simple on a first read. Postgres aggregate functions are the mechanism that turns a million-row table into the single number your stakeholder wants — and the gap between "looks right" and "is right" is usually about three inches wide, hidden inside NULL handling, COUNT variants, or a HAVING clause in the wrong place.
Most reporting bugs aren't logic errors. They're semantic errors: the developer asked a slightly different question than they thought they asked, and PostgreSQL answered it correctly. The query ran, the result looked plausible, and nobody caught it until a finance team member compared two reports and found a discrepancy. Understanding the rules that govern aggregate behavior is the fastest way to close that gap.
What Postgres Aggregate Functions Actually Do to a Set of Rows
Why This Is More Than a Fancy Total
An aggregate function takes a set of rows and collapses them into a single return value. That's the definition. The part that causes trouble is what "a set of rows" means in context, because PostgreSQL applies aggregates after the `FROM`, `JOIN`, and `WHERE` clauses have already filtered and shaped the working set — but before `HAVING` and `ORDER BY` run. The mental model of "aggregate = total" skips over that sequencing, and the sequencing is where bugs live.
When there's no `GROUP BY`, every row in the working set is one group. When there is a `GROUP BY`, PostgreSQL partitions the working set into buckets — one per unique combination of the grouped columns — and applies the aggregate independently inside each bucket. The result set changes shape: instead of one row per input row, you get one row per group. That shape change is the whole point, and it's also where a wrong mental model starts producing wrong numbers.
What This Looks Like in Practice
Take a minimal `sales` table with columns `region`, `product`, and `revenue`. A query like `SELECT SUM(revenue) FROM sales` collapses every row into a single total. Change it to `SELECT region, SUM(revenue) FROM sales GROUP BY region` and now PostgreSQL builds one bucket per distinct region value and sums revenue inside each. The output has as many rows as there are distinct regions — not one, not all.
The business question drives which shape you need. "What is total revenue?" is a single-row answer. "What is revenue by region?" is a multi-row answer. The aggregate function is the same in both cases; the `GROUP BY` is what changes the question. Getting that distinction wrong means your query is technically valid but answering something nobody asked.
The Five Postgres Aggregate Functions You Reach for First Are Not Interchangeable
MIN, MAX, SUM, AVG, and COUNT Each Answer a Different Question
`COUNT` asks: how many? `SUM` asks: how much in total? `AVG` asks: what is the central tendency? `MIN` and `MAX` ask: what are the extremes? Those sound like synonyms when you're writing a simple report, but they diverge fast when the data has NULLs, skewed distributions, or unexpected types.
`SUM` on an integer column returns a `bigint` in PostgreSQL — the database widens the type to avoid overflow. `AVG` on an integer column returns a `numeric`, not a float, which means precision is preserved but the return type is different from what you might expect if you're passing the result into application code that expects a float. `MIN` and `MAX` work on any orderable type, including text and timestamps, which is useful but means the return type matches the column type exactly — no widening.
What This Looks Like in Practice
Imagine a report that needs three metrics per order status: row count, total revenue, and average order value. `COUNT()` gives the row count. `SUM(revenue)` gives total revenue. `AVG(revenue)` gives average order value. These are three different questions, and using the wrong function gives a result that is numerically valid but semantically wrong. Substituting `SUM(revenue) / COUNT()` for `AVG(revenue)` looks equivalent — and it is, unless some `revenue` values are NULL. Then `AVG` skips the NULLs in its denominator automatically, while your manual division divides by the full row count. The results diverge, and the `AVG` version is almost certainly the one that matches the business intent.
The coercion detail matters in production: if downstream code casts `AVG` output to a float and the column is integer, the `numeric` return type will coerce without error but may behave differently across ORMs and drivers than an explicit `FLOAT` cast would.
COUNT(\*), COUNT(column), and COUNT(DISTINCT ...) Behave Differently for a Reason
COUNT(\*) Counts Rows, Not Values
`COUNT()` counts every row in the group, regardless of what any column contains. A row where every column is NULL still increments `COUNT()`. This is the right function when the question is "how many records exist?" — not "how many records have a value in this column?"
The asterisk is not a wildcard in the SQL sense here; it's syntactic shorthand meaning "count the row itself, not any expression." PostgreSQL evaluates no column value to answer `COUNT(*)`. That makes it fast and unambiguous, which is exactly why it's the right choice for row counts.
COUNT(column) Ignores NULLs, Which Is Exactly Why It Can Mislead You
`COUNT(column)` counts the number of non-NULL values in that column within the group. If ten rows exist and three have a NULL in the target column, `COUNT(column)` returns 7. That's not a bug — it's the documented behavior. The bug is assuming it returns 10 because you saw ten rows come back from a `SELECT *`.
This matters most in reporting: if `customer_id` is NULL for guest checkouts, `COUNT(customer_id)` silently undercounts your registered-customer orders. The query runs, the number looks reasonable, and the undercount only surfaces when someone cross-references with a CRM export.
What This Looks Like in Practice
Consider a table `orders` with five rows:
Running three COUNT variants against this table:
Returns:
- `total_rows`: 5
- `registered_customers`: 3
- `unique_customers`: 3
Now add a duplicate: if `customer_id = 101` appears in two rows, `COUNT(customer_id)` returns 4 but `COUNT(DISTINCT customer_id)` still returns 3. The distinction between "how many orders from known customers" and "how many unique known customers placed orders" is real, and only `COUNT(DISTINCT ...)` answers the second question correctly.
PostgreSQL NULL Handling in Aggregates Is Where Clean-Looking Queries Go Wrong
NULLs Disappear in Some Places and Matter a Lot in Others
PostgreSQL aggregate functions — `SUM`, `AVG`, `MIN`, `MAX`, and `COUNT(column)` — all ignore NULL values in their input set. This is consistent and intentional: a NULL represents an unknown value, and including an unknown in a sum or average would produce an unknown result. PostgreSQL chooses to skip it instead.
The trap is that "ignored" does not mean "treated as zero." `SUM` over a column with three values and two NULLs returns the sum of the three values, not the sum of five values where two happen to be zero. `AVG` divides by the count of non-NULL values, not by the total row count. Those are different denominators, and in skewed data they produce meaningfully different results.
What This Looks Like in Practice
Using the same `orders` table: `SUM(revenue)` returns 290.00 (all five revenue values are non-NULL in this case). But `AVG(revenue)` returns 58.00 — the sum divided by 5. Now imagine `revenue` is NULL for the guest checkout rows:
`SUM(revenue)` now returns 215.00. `AVG(revenue)` returns 71.67 — the sum of three non-NULL values divided by 3, not by 5. That average is technically correct for the rows that have revenue, but it may not be the business average you want if guest orders should count as zero-revenue events rather than unknown-revenue events. The fix is explicit: `AVG(COALESCE(revenue, 0))` treats NULLs as zeros, which changes the denominator back to 5.
The failure mode gets worse after a `LEFT JOIN`. When you join `orders` to a `customers` table and some orders have no matching customer, the joined customer columns come back as NULL. Aggregating on those columns now mixes "genuinely missing data" with "this order had no customer record" — and those are not the same thing. `COUNT(customer_name)` after a left join silently drops the unmatched rows from the count, which may or may not be what the report requires.
GROUP BY and HAVING Are Not the Same Filter, and Postgres Will Not Forgive the Mix-Up
GROUP BY Builds the Buckets; HAVING Decides Which Buckets Survive
PostgreSQL executes a `SELECT` statement in a defined logical order: `FROM` and `JOIN` first, then `WHERE`, then `GROUP BY`, then aggregate functions, then `HAVING`, then `SELECT` expressions, then `ORDER BY`, then `LIMIT`. `WHERE` filters individual rows before any grouping happens. `HAVING` filters groups after aggregation has already run. That sequencing is not a style choice — it's the rule, and violating it produces either a syntax error or a subtly wrong result.
The practical consequence: you cannot use an aggregate function in a `WHERE` clause. `WHERE COUNT() > 3` is invalid in PostgreSQL. `HAVING COUNT() > 3` is correct, because by the time `HAVING` runs, the count per group already exists as a computed value.
What This Looks Like in Practice
Finding customers who have placed more than three orders:
The second query fails immediately. But the subtler version of this bug is using `WHERE` to filter on a column that could have filtered groups instead:
This query is valid and may even be intentional — you only want to count completed orders. But if the business question is "customers who have more than three orders of any status," the `WHERE` clause is removing rows too early, and the `HAVING` count is operating on a truncated set.
Why the Wrong Clause Makes Good Data Disappear
The bug is quiet: the query runs, returns rows, and looks correct. Only when you compare it to a version without the premature `WHERE` filter do you notice that some customers with four mixed-status orders are missing from the result. `WHERE` is not wrong — it's just answering a different question than the one you thought you asked. The question "which groups pass this threshold?" belongs in `HAVING`. The question "which rows should participate in grouping?" belongs in `WHERE`.
Conditional Postgres Aggregate Functions Are Cleaner with FILTER (WHERE ...), Not CASE Gymnastics
CASE Still Works, but FILTER Is the Postgres-Native Move
The traditional approach to conditional aggregation uses `CASE` inside an aggregate:
This works, and it's portable across databases. The problem is readability: a query with four or five conditional aggregates turns into a wall of nested `CASE` expressions where the actual question — "what is paid revenue?" — is buried inside the syntax.
PostgreSQL's `FILTER (WHERE ...)` clause attaches directly to an aggregate and reads like the question it's answering:
The aggregate still skips NULLs. The filter runs before the aggregate sees the value — rows that don't match the filter condition are excluded from that aggregate's input set entirely, which means they can't inflate the count or the sum.
What This Looks Like in Practice
A report on order revenue by status:
The same query with `CASE` is longer, harder to scan, and easier to introduce an off-by-one error in the `ELSE` branch. `FILTER` also connects naturally to the join inflation problem: when a join produces duplicate rows, a `CASE ELSE 0` still includes those rows in a `COUNT(*)`, while `FILTER` on a specific condition at least makes the inclusion explicit. The real fix for join inflation is upstream, but `FILTER` makes the intent of each aggregate unambiguous.
JOINs Are Where Aggregate Results Quietly Get Inflated
Duplicates After Joins Are Usually a Model Problem, Not an Aggregate Problem
A join does exactly what you ask. If you join `orders` to `order_items` on `order_id`, and each order has three items, every order row appears three times in the joined result set. Then `SUM(revenue)` on that joined set triples every order's revenue contribution. The aggregate is correct given its input — the input is just not what you intended.
This is a grain mismatch: the `orders` table has one row per order, the `order_items` table has one row per item, and joining them without aggregating first produces a result at the item grain, not the order grain. Aggregating revenue at the item grain inflates the total.
What This Looks Like in Practice
The pre-aggregation subquery pattern is the clean fix: aggregate the many-side table first, then join the result to the one-side table. The grain of the join now matches the grain of the question.
Why DISTINCT Is Not a Magic Eraser
`COUNT(DISTINCT order_id)` after a bad join will give you the right count of orders. It will not fix `SUM(revenue)` — distinct doesn't apply to sums the way you might hope. And reaching for `DISTINCT` to fix inflated results is a signal that the join grain is wrong, not that the aggregate is wrong. Fix the model; don't paper over it with `DISTINCT`.
The Advanced Postgres Aggregate Functions Are Worth Knowing Before Your Reports Get Serious
Percentiles, Mode, and Statistical Aggregates Solve Questions Averages Cannot
`AVG` is the right function until the data is skewed. Page load times, order values, and salary data all tend to have long right tails: a few extreme values pull the mean up significantly while the typical experience sits much lower. The median — the 50th percentile — is a better representation of the central experience in those cases.
PostgreSQL provides `percentile_cont(0.5) WITHIN GROUP (ORDER BY value)` for the continuous median and `percentile_disc` for the discrete version. `mode() WITHIN GROUP (ORDER BY value)` returns the most frequent value. These are ordered-set aggregates, which means they require the `WITHIN GROUP` syntax rather than a plain column argument.
What This Looks Like in Practice
If 95% of page loads complete in under 800ms but a few outliers run at 10 seconds, the mean might be 1,200ms — a number that represents almost no user's actual experience. The median and p95 tell a more honest story.
Why Grouping Sets and ROLLUP Matter When One Report Needs Several Grains
`ROLLUP` generates subtotals and grand totals in a single query. `GROUPING SETS` lets you specify exactly which combinations of columns to aggregate across. Without these, producing a report that shows revenue by region, by product, and in total requires three separate queries unioned together — or application-side aggregation of the raw grouped result.
This returns rows grouped by `(region, product)`, rows grouped by `region` alone (subtotals), and a single grand total row where both columns are NULL. The NULL in the grouping column marks the rollup row — use `GROUPING()` to distinguish intentional NULLs in data from rollup-generated NULLs.
On the performance side: PostgreSQL chooses between hash aggregation and sort-based aggregation depending on the data size, available memory, and whether an index exists on the grouped columns. Hash aggregation is faster for smaller working sets; sort aggregation is used when the input is already ordered or when the hash table would exceed `work_mem`. For large analytical queries, raising `work_mem` at the session level can push the planner toward hash aggregation and avoid a disk spill.
FAQ
Which PostgreSQL Aggregate Functions Should I Use Most Often, and What Does Each One Return?
The core five are `COUNT`, `SUM`, `AVG`, `MIN`, and `MAX`. `COUNT(*)` returns `bigint`. `SUM` on integer input returns `bigint`; on numeric input it returns `numeric`. `AVG` always returns `numeric` for integer and numeric inputs, `double precision` for floating-point inputs. `MIN` and `MAX` return the same type as the input column. Map each function to its question: row count, total, central tendency, lower bound, upper bound — and treat them as distinct tools rather than interchangeable siblings.
How Do COUNT(\*), COUNT(column), and COUNT(DISTINCT column) Differ in PostgreSQL?
`COUNT(*)` counts every row in the group, including rows where all columns are NULL. `COUNT(column)` counts only rows where that column is non-NULL — which means it silently undercounts if the column has missing values. `COUNT(DISTINCT column)` counts unique non-NULL values. The difference becomes critical after joins: a left join that produces NULL-filled columns will cause `COUNT(column)` to drop those rows from the count, which may or may not match the business intent.
When Should I Use GROUP BY Versus HAVING with an Aggregate?
`GROUP BY` partitions the working set into buckets before aggregation runs. `HAVING` filters those buckets after aggregation. If the predicate references an aggregate function — `COUNT(*) > 3`, `SUM(revenue) > 1000` — it belongs in `HAVING`. If it filters raw rows — `status = 'completed'` — it belongs in `WHERE`, which runs before grouping. Using `WHERE` where you need `HAVING` either causes a syntax error or silently removes rows from the group before the aggregate sees them, changing the question the query answers.
How Do I Write Conditional Aggregates in PostgreSQL Without Inflating Counts After Joins?
Use `FILTER (WHERE ...)` attached directly to the aggregate rather than a `CASE` expression inside it. `FILTER` excludes non-matching rows from the aggregate's input set entirely, making the intent explicit and reducing the risk of accidentally including join-inflated rows in a count. For join inflation itself, the correct fix is pre-aggregating the many-side table before joining, so the grain of the join matches the grain of the report. `DISTINCT` can patch a count, but it won't fix an inflated `SUM`.
What Are the Most Important Interview Talking Points About Aggregate Functions in Postgres?
Four areas separate a shallow answer from a solid one. First, NULL handling: aggregates skip NULLs by default, which is intentional but can produce misleading results when NULLs mean different things in different columns. Second, COUNT variants: `COUNT(*)`, `COUNT(column)`, and `COUNT(DISTINCT column)` answer three different questions and are not interchangeable. Third, GROUP BY versus HAVING: the execution order determines which clause the predicate belongs in, and confusing them either breaks the query or silently changes what it computes. Fourth, join inflation: a one-to-many join produces more rows than the one-side table has, and aggregating without accounting for the grain change inflates totals. Candidates who can explain the mechanism behind each of these — not just name them — demonstrate real working knowledge.
Conclusion
The opening problem is still the real problem: postgres aggregate functions look simple right up until NULLs, joins, and clause order start returning numbers that are technically correct and semantically wrong. The query ran. The result looked plausible. And somewhere downstream, a report is lying to someone who trusts it.
Before you ship the next aggregate query, run it against the rules from this guide. Check which COUNT variant you're using and whether any of the counted columns can be NULL. Check whether your filter belongs in `WHERE` or `HAVING` by asking whether it references a computed aggregate. Check the join grain — if you're joining a one-to-many relationship, confirm you're aggregating at the right level before or after the join. Check whether `AVG` is the right measure or whether the distribution is skewed enough to need a percentile. One pass through those questions is faster than debugging a discrepancy after the report is already in production.
How Verve AI Can Help You Prepare for Your Backend Developer Interview
If this guide is prep material for a job interview, the hardest part isn't learning the rules — it's reconstructing your reasoning under live pressure when a follow-up question pushes past the definition. That's exactly the gap Verve AI Interview Copilot is built to close. During a real interview on Zoom, Google Meet, or Teams, it follows the conversation and helps you structure answers in real time — so when an interviewer asks "walk me through how you'd handle NULL inflation after a left join," you have the architecture of a clear answer, not just a memory of having read about it. The desktop app stays invisible during screen share, so the support is there without changing how the interview looks to the other side. To rehearse before the real thing, Verve AI Interview Copilot's separate Mock Interviews feature lets you run the format against realistic backend questions — COUNT variants, GROUP BY versus HAVING, join grain problems — before the day that counts.
James Miller
Career Coach
