SQL
Beginner 13 min read SQL

Aggregates and Grouping

Collapsing rows without collapsing meaning

Aggregation collapses many rows into summary values at a chosen grain. Understanding grain and which aggregation to use per column is the difference between a useful metric and a misleading one.

GROUP BYSUMAVGCOUNTMINMAXgrain
Prerequisites: SQL for Analytics
Before we start

What you will learn

  • Choose the right aggregate per column type
  • Understand how grain changes the meaning of a summary
  • Avoid the avg-of-averages and sum-of-rounding traps
Introduction

What is this?

Aggregates reduce a set of rows into summary values: SUM when adding is meaningful, AVG for typical values, COUNT for volume, MIN/MAX for ranges. GROUP BY picks the grain (the level at which the summary is computed). Aggregation is where most BI numbers are created — and where most errors enter.

Motivation

Why do you need it?

  • Dashboards are summaries; almost everything a viewer sees is an aggregate.
  • Grain mistakes double or halve headline metrics silently.
  • Average-of-averages is one of the most common BI fallacies.
  • Choosing the wrong aggregate (summing rates) makes perfect math into nonsense.
Think about it

Mental model

Imagine

Mixing fuel for a road trip

Think of

Aggregation decisions

You total the gallons (SUM), check the typical tank (AVG), and note extremes (MIN/MAX). But averaging per-trip mileage across trips of different lengths is like averaging a bakery's loaves and croissants — the composition matters.

Mechanics

How it works

  • GROUP BY partitions rows by the grouping columns; each group produce one output row.
  • SUM adds a numeric measure; AVG is SUM/COUNT (with NULLs excluded).
  • COUNT(*) counts rows; COUNT(column) counts non-null values.
  • MIN/MAX find extremes per group regardless of order.
  • Result grain = grouping columns; the metric's meaning is defined by that grain.

Rows to groups under GROUP BY.

Raw rows
Partition by region
Aggregate each group
One row per region
Hands-on

Examples

AGGRAVATED: right and wrong average

AGGRAVATED: right and wrong average
-- Wrong: average of already-averaged daily rates (avg of averages)
SELECT AVG(daily_conv_rate) FROM (SELECT date, COUNT(orders)/COUNT(sessions) AS daily_conv_rate FROM f GROUP BY date);
 
-- Right: pool the underlying events first
SELECT COUNT(orders) * 1.0 / COUNT(sessions) AS conv_rate FROM f;

Weighting matters when groups differ in size: averaging per-day rates equally weights quiet and busy days. Pooling the counts first computes the true rate.

Real world

In production

  • A revenue report sums GMV by month; refunds are excluded at the row level before SUM.
  • A convo metric pools orders/sessions rather than averaging daily rates, so busy days matter more.
  • A support SLA metric computes p95 of a duration column via the tool's percentile aggregate, not AVG.
Guidelines

Best practices

  • Match the aggregate to the column semantics: additive measures sum; rates and ratios pool, not average.
  • Always note the output grain — month/region is not month/country.
  • For AVERAGE of a ratio, pool counts; only average when groups are equal-weighted.
  • Sum before rounding; never round columns and then SUM.
Watch out

Common mistakes

  • AVG of a daily rate, weighting every day equally regardless of volume.
  • SUM on a percentage column, manufacturing percentages over 100%.
  • Rounding in the SELECT list then aggregating the rounded values.
  • Forgetting NULLs: AVG ignores them, changing the denominator.
Root cause analysis

Common issues

Error
Conversion rate looks too low
Root cause

Daily rates were averaged (quiet days weighted as equals)

Fix

Pool numerator and denominator, then divide.

Error
Sum of category values exceeds the grand total
Root cause

VALUES in multiple groups (double counting)

Fix

Choose non-overlapping grouping keys or use COUNT(DISTINCT).

When things break

Debugging tips

  • Sanity-check with a single group: does SUM over one region match the source?
  • Recreate a small window by hand and confirm the aggregate.
  • Check the grain by GROUP BY columns count — more groupings, finer grain, bigger result.
  • For weird averages, compute both AVG-approach and pooled approach and compare.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Which approach correctly computes a time-series conversion rate?

2

AVG ignores NULL values for the column it averages.

Go deeper

Official documentation

PostgreSQL aggregate functions
docs