GROUP BY

GROUP BY collapses rows that share the same value in one or more columns into a single output row per group. On its own it doesn't do much — the real power shows up when you combine it with aggregate functions like COUNT, SUM, AVG, MIN, and MAX, which then compute one summary value per group instead of one value for the whole table.

Think of it as an instruction to the database: "bucket the rows by this value, then summarize each bucket." Grouping customers by country and counting orders per country, or grouping sales by product category and summing revenue per category, are both classic GROUP BY use cases.

Basic syntax

orders

SQL

SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status;

status    | order_count
----------+-------------
pending   |          12
shipped   |          34
delivered |         148
cancelled |           6

Every distinct value of status becomes its own group, and COUNT(*) is computed separately within each group.

The rule PostgreSQL strictly enforces

Once a query has a GROUP BY clause, every column in the SELECT list must either:

Appear in the GROUP BY clause itself, or
Be wrapped in an aggregate function (COUNT, SUM, AVG, MIN, MAX, etc.).

This fails in PostgreSQL

SQL

SELECT customer_id, order_date, COUNT(*)
FROM orders
GROUP BY customer_id;

-- ERROR:  column "orders.order_date" must appear in the GROUP BY clause
--         or be used in an aggregate function

order_date isn't in the GROUP BY list and isn't aggregated, so PostgreSQL has no single well-defined value to return for it once many rows with different dates have been collapsed into one customer_id group. Fix it by adding the column to GROUP BY, or by aggregating it (for example MAX(order_date) to get the most recent order date per customer):

SQL

SELECT customer_id, MAX(order_date) AS last_order_date, COUNT(*)
FROM orders
GROUP BY customer_id;

This is a safety feature, not a limitation

Some other databases (most notoriously older versions of MySQL) will silently accept a query like the broken one above and hand back some arbitrary row's order_date for each group — with no guarantee about which row it picked. PostgreSQL refuses to guess. It raises a compile-time error instead of returning a result that looks correct but is actually nondeterministic. This catches a whole class of subtle reporting bugs before they ever reach production — treat the error as PostgreSQL doing you a favor, not as the database being difficult.

Grouping by multiple columns

You can group by more than one column. Each unique combination of values across those columns forms its own group — this is how you produce a breakdown by two (or more) dimensions at once, such as revenue per region per month.

sales

SQL

SELECT region, category, SUM(amount) AS total_sales
FROM sales
GROUP BY region, category
ORDER BY region, category;

region | category    | total_sales
-------+-------------+-------------
east   | electronics |     18420.00
east   | furniture   |      9310.00
west   | electronics |     22100.00
west   | furniture   |     11250.00

Note

The order of columns in GROUP BY doesn't change the result, only the grouping granularity — GROUP BY region, category and GROUP BY category, region produce the same set of groups. Use ORDER BY separately to control the row order in the output.

GROUP BY runs after WHERE, before ORDER BY

Understanding query execution order helps a lot here: PostgreSQL conceptually filters rows with WHERE first, then forms groups with GROUP BY, then computes aggregates, and only after that applies ORDER BY (and LIMIT) to the grouped results. To filter groups themselves — for example, only showing categories with more than 10 orders — you need HAVING, covered on the next page.

GROUP BY collapses rows sharing the same value(s) into one row per group.
Every SELECTed column must be in GROUP BY or wrapped in an aggregate — PostgreSQL enforces this at compile time.
Grouping by multiple columns groups by the unique combination of those column values.
WHERE filters rows before grouping; HAVING filters groups after aggregation.