Aggregation

Collapsing many rows down into a single summary value — a count, a total, an average — instead of listing every individual row.

What is it?

Sometimes you don't want the individual rows at all — you want a summary across them. "How many orders total?" "What's the total revenue?" "What's the average order value per customer?" These are aggregation questions: turning many rows into one (or a few) summary numbers.

SQL provides aggregate functions like COUNT, SUM, AVG, MIN, and MAX for exactly this. On their own, they summarize an entire table into one row. Paired with GROUP BY, they summarize per group — one summary row per distinct value in a column, like "total revenue per customer" instead of one grand total across everyone.

Explain like I'm 10

If a table of orders is a pile of individual receipts, aggregation is adding them all up on a calculator to get one total. GROUP BY is sorting the receipts into piles by customer first, then running the calculator on each pile separately, giving you one total per customer instead of one total overall.

Examples

A single summary value

SELECT COUNT(*) AS total_orders, SUM(total_cents) AS revenue_cents
FROM orders;

Collapses the entire orders table into one row: how many orders exist, and their combined total.

Summarizing per group

SELECT user_id, COUNT(*) AS order_count, SUM(total_cents) AS revenue_cents
FROM orders
GROUP BY user_id;

Instead of one grand total, this produces one row per distinct user_id — each with that user's own order count and revenue total.

How it works

Without GROUP BY, an aggregate function scans every row that survives any WHERE clause and folds them into a single result. With GROUP BY <column>, the database first buckets rows by that column's value, then runs the aggregate function separately within each bucket, producing one result row per bucket.

A HAVING clause can then filter those group results — for example, "only show customers with more than 5 orders" — which is different from WHERE, which filters individual rows before grouping happens.

Why does it exist?

Individual rows are often too granular to be useful for decisions — nobody reads a million individual order rows to understand revenue. Aggregation exists so the database can do that summarizing work directly, efficiently, and correctly, instead of every application pulling all the raw rows and computing totals in its own code.

When to use it

Reach for aggregation any time you need a count, total, average, or extreme value — dashboards, reports, "top N by revenue" style features, or any question phrased as "how many," "how much," or "on average."

When not to use it

If you need the individual rows themselves (not a summary), aggregation isn't the tool — a plain SELECT with WHERE and ORDER BY is. Aggregating extremely large tables on every request without caching can also be slow; some apps precompute and store summaries instead of recalculating them live every time.

Common mistakes

  • Selecting a non-aggregated, non-grouped column alongside an aggregate function, which most databases reject or handle unpredictably.

  • Using WHERE to try to filter on an aggregate result (like WHERE COUNT(*) > 5) instead of the required HAVING clause.

  • Forgetting that COUNT(*) counts rows including ones with empty values, while COUNT(column_name) skips rows where that specific column is empty.

Practice exercises

  1. Easy:

    Write a query that returns the total number of rows and the average price from a products table.

  2. Medium:

    Write a query using GROUP BY to show the number of orders placed per user_id in an orders table.

  3. Hard:

    Extend the previous query with a HAVING clause that only shows users with more than 3 orders.

Interview questions

What does GROUP BY do?

It buckets rows by the value of one or more columns, then runs any aggregate functions separately within each bucket, producing one result row per group.

What's the difference between WHERE and HAVING?

WHERE filters individual rows before grouping happens; HAVING filters the summarized group results after aggregation.

What's the difference between COUNT(*) and COUNT(column_name)?

COUNT(*) counts every row regardless of empty values; COUNT(column_name) only counts rows where that specific column has a non-empty value.