SQL-01-007-P · Primer — Grouping Rows with GROUP BY

The idea in one sentence

A plain aggregate like SUM(amount) collapses your whole table into a single number, but GROUP BY tells the database to first sort the rows into buckets — one bucket per distinct region, per customer, per day — and then compute that aggregate once inside each bucket, so instead of one grand total you get one answer row per group.

Why it exists

Almost every real question about data is a "per something" question. Not "how much did we sell?" but "how much did we sell per region?" Not "how many orders exist?" but "how many orders per customer?" A raw table stores one row per event — one row per sale, one row per signup — and staring at ten thousand rows tells you nothing. You need to fold those rows down into a summary, but you rarely want a single grand total; you want the summary broken out by category.

GROUP BY exists to do exactly that folding. Without it, aggregate functions (COUNT, SUM, AVG, MIN, MAX) can only produce one value for the entire result set. With it, you draw lines through the table — "put all the East rows together, all the West rows together" — and the aggregate is computed separately on each pile. This is the single most common shape of question in reporting, dashboards, and analytics, which is why GROUP BY shows up in a huge fraction of the SQL anyone writes at work. It turns "here is every event" into "here is what happened, summarized the way a human wants to read it."

How it works

Think of GROUP BY as a two-step machine. Step one: it looks at the column (or columns) you name and shuffles the rows into groups so that every row sharing the same value lands in the same group — all the East rows in one pile, all the West rows in another. Step two: for each pile it runs your aggregate functions once, producing exactly one output row per group. The columns in your SELECT therefore split into two roles: the grouping columns (which you may show directly, because every row in the group shares that value) and the aggregates (which summarize the group). The rule that trips up beginners: any column in SELECT that is not wrapped in an aggregate must appear in the GROUP BY — otherwise the database can't decide which of the many values in the group to show you. Here's the whole idea in one runnable query:

CREATE TABLE sales (id INTEGER, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
  (1, 'East', 100),
  (2, 'West', 250),
  (3, 'East', 300),
  (4, 'West', 150),
  (5, 'East', 200);

SELECT region,
       COUNT(*)    AS n_orders,
       SUM(amount) AS total
FROM sales
GROUP BY region
ORDER BY region;

-- region | n_orders | total
-- East   |    3     |  600
-- West   |    2     |  400

The five raw rows collapsed into two summary rows — one per region — and each aggregate was computed only over the rows in its own group.

The one gotcha

Every non-aggregated column in your SELECT must be in the GROUP BY — and if you break that rule, some databases quietly lie to you instead of erroring. In strict databases like PostgreSQL, writing SELECT region, id, SUM(amount) FROM sales GROUP BY region throws an error, because a group like East contains three different id values (1, 3, 5) and the engine has no way to pick one — which is correct and helpful. But older MySQL configurations and SQLite will happily run that query, silently returning some arbitrary, unpredictable id from each group with no warning. That's the dangerous version: your query "works," your dashboard shows numbers, and one of the columns is meaningless noise picked at random per group. The mental fix is simple and portable: for each column in your SELECT, ask "is this either grouped or aggregated?" If a column is neither, you don't actually know what value you want — so decide whether it belongs in the GROUP BY (you want to split further by it) or inside an aggregate like MIN, MAX, or COUNT (you want to summarize it).

See it — five rows fold into one summary row per group

flowchart TD
    A["sales table<br/>5 rows"] --> B{"GROUP BY region"}
    B --> C["Group: East<br/>ids 1, 3, 5"]
    B --> D["Group: West<br/>ids 2, 4"]
    C --> E["Aggregate per group<br/>COUNT=3, SUM=600"]
    D --> F["Aggregate per group<br/>COUNT=2, SUM=400"]
    E --> G["Result: 2 rows<br/>one per region"]
    F --> G

The one-line takeaway

GROUP BY sorts rows into buckets by the columns you name and computes each aggregate once per bucket, so every column you SELECT must be either a grouping column or wrapped in an aggregate.

Where you'll meet it

You'll meet GROUP BY the moment your first report goes past "how many total?" — the instant a stakeholder asks for revenue by region, active users by month, or error counts by service, you are writing a GROUP BY. You'll meet it powering nearly every chart on a dashboard, because a bar chart is literally "one aggregate per category" and a time series is "one aggregate per day." You'll meet the gotcha the first time a query works on your SQLite laptop and then errors on the production PostgreSQL database, or worse, the first time a "working" report shows a column of random-looking values because it was neither grouped nor aggregated. Interviewers lean on it hard: "what must every column in a GROUP BY query be?", "why does this query error in Postgres but not MySQL?", and the classic follow-up — "now show me only the groups with more than five rows" — which walks you straight into HAVING, the clause that filters groups after they're formed. Master the two-step machine, and half of everyday analytical SQL falls into place.

Named concepts: GROUP BY, per-group aggregates, grouping columns in SELECT