GROUP BY

What GROUP BY Does

GROUP BY collapses rows that share the same value(s) in one or more columns into a single output row per group. It is almost always paired with an aggregate function (SUM, COUNT, AVG, MIN, MAX — see Chapter 14) that is computed once per group instead of once per row.

Without GROUP BY, an aggregate function collapses the entire table into one row. With GROUP BY, the table is first partitioned into buckets by the grouping column(s), and the aggregate runs separately inside each bucket.

Running Example

All examples in this chapter use one small orders table:

sql
CREATE TABLE orders (
    id      INT PRIMARY KEY,
    region  VARCHAR(10),
    product VARCHAR(10),
    amount  NUMERIC(8,2)
);

Total of all 8 rows: 1060. Per region: East = 310, West = 550, North = 200. Per product: Widget = 530, Gadget = 530 (100+200+50+120+60 and 150+300+80).

Basic Syntax

sql
SELECT region, SUM(amount) AS total_amount
FROM orders
GROUP BY region
ORDER BY region;

Result:

Three input groups (East, North, West) produce exactly three output rows, no matter how many raw orders rows fed into each group (East had 3 rows, North had 2, West had 3).

The Golden Rule

Every column in the SELECT list must be either:

1. Listed in GROUP BY, or
2. Wrapped in an aggregate function.

sql
-- INVALID in standard SQL / PostgreSQL (product is neither grouped nor aggregated)
SELECT region, product, SUM(amount) FROM orders GROUP BY region;
-- ERROR: column "orders.product" must appear in the GROUP BY clause
--        or be used in an aggregate function

MySQL historically allowed this (returning an arbitrary, unpredictable product value per group) unless ONLY_FULL_GROUP_BY mode is enabled — but relying on that behavior is a bug waiting to happen and is disabled by default since MySQL 5.7.

Adding More Aggregates

sql
SELECT region,
       COUNT(*)      AS order_count,
       SUM(amount)   AS total_amount,
       AVG(amount)   AS avg_amount
FROM orders
GROUP BY region
ORDER BY region;

Execution Order Reminder

Conceptually GROUP BY runs after FROM/JOIN/WHERE and before HAVING, SELECT's final projection, ORDER BY, and LIMIT. This is why you cannot reference a SELECT-list alias defined by an aggregate inside the same query's WHERE clause — WHERE doesn't know about groups yet.

Edge Cases

• Grouping on a NULL-containing column: all NULL values in the grouping column are treated as one single group (NULLs are considered "equal" to each other for grouping purposes, unlike in most comparisons).
• Empty table: GROUP BY on an empty table produces zero rows — not one row of zeros. (Contrast with no GROUP BY at all, where COUNT(*) on an empty table still returns one row with 0.)
• GROUP BY an expression: you can group by a computed expression, e.g. GROUP BY EXTRACT(YEAR FROM order_date), as long as the same expression (or its alias, in PostgreSQL/MySQL) is used consistently.
• Grouping by primary key: if you GROUP BY id (a unique column) every group has exactly one row, so aggregates are technically pointless but legal.

Key Takeaways / Interview Q&A

• Q: What happens if I select a non-aggregated, non-grouped column?

A: Standard SQL and PostgreSQL reject the query at parse time; MySQL (default mode) also rejects it since 5.7's ONLY_FULL_GROUP_BY.

• Q: Does GROUP BY guarantee row order?

A: No. Always add ORDER BY if you need a specific group order.

• Q: How many output rows does `GROUP BY region` produce here?

A: 3 — one per distinct region value (East, North, West).

• Q: Can you GROUP BY a column not in SELECT?

A: Yes — you can group by columns purely for bucketing without displaying them, as long as the SELECT list rules above are respected for the columns you do display.