GROUP BY

GROUP BY: one row per bucket

A single aggregate gives you one number for the whole table. But nobody wants "total revenue" alone. They want revenue per category, per month, per region. GROUP BY is the operator that turns one grand total into one row per bucket. It is, quite literally, the shape of a mart: a fact-like table where each row is a category and each column is a measure.

Here's the mental model. GROUP BY category slices the table into piles, one pile per distinct category, then runs your aggregates once per pile:

SELECT
  category,
  COUNT(*)          AS product_count,
  AVG(price_cents)  AS avg_price_cents
FROM products
GROUP BY category;

Input of 20 product rows across 4 categories → output of exactly 4 rows, one per category, each carrying that category's count and average.

AVG(value) GROUP BY category — Rows

Step 1 / 3

• audio1500
• audio3000
• audio4500
• cables500
• cables1000

• audio1500 + 3000 + 4500
• cables500 + 1000

5 input rows.

Anatomy of a grouped query:

SELECT   category,           COUNT(*),  AVG(price_cents)
         └── grouping key ──┘ └──── aggregates over each group ────┘
FROM     products
GROUP BY category
         └── one output row per distinct value (or combination) here ──┘

The grain. The single most important thing to say out loud about any grouped query is its grain: "one row per ___." GROUP BY category → one row per category. GROUP BY category, year_month → one row per (category, month) combination. The grain is the list of columns in your GROUP BY. Declaring it keeps you honest about what a row means, and it's the first question any reviewer will ask of your mart.

Multi-column grouping just means the pile is defined by a combination:

SELECT
  category,
  strftime('%Y-%m', order_ts) AS year_month,
  SUM(revenue_cents)          AS revenue_cents
FROM sales
GROUP BY category, strftime('%Y-%m', order_ts);

One row per category per month. That's a monthly revenue mart in five lines.

The rule that generates half of all GROUP BY errors: every column in your SELECT list must be either inside an aggregate or listed in the GROUP BY. Why? Because the output has one row per group, so a bare, non-grouped column like product_name has no single value to show for a whole category; there could be dozens of different names in the pile. Standard SQL (Postgres, SQL Server) rejects the query outright.

In the warehouse this differs. SQLite is lax: it will silently pick an arbitrary row's value for an ungrouped column instead of erroring (MySQL in non-strict mode does the same). Postgres, Snowflake, BigQuery, and SQL Server all raise column must appear in the GROUP BY clause. Don't lean on SQLite's leniency. Write the query as if it will be rejected, because in production it will be. If you truly want one representative value, wrap it in MIN()/MAX() to make the choice explicit.

Keep it readable / common pitfall: if you group by a computed expression (like strftime(...)), put the same expression in both SELECT and GROUP BY. You cannot reference the SELECT alias inside GROUP BY in standard SQL: the alias isn't defined yet when GROUP BY runs. (SQLite and Postgres happen to allow the alias; Oracle and SQL Server do not, so repeat the expression to stay portable.)

Recap: GROUP BY collapses each distinct key (or key combination) into one output row and runs your aggregates per group; the grain is your grouping columns, and every selected column must be either aggregated or grouped.