SQL / AGGREGATION WITH GROUP BY
Grouping on expressions instead of raw columns
Group rows by a computed value such as a month bucket, CASE tier, or normalized string, and keep the SELECT list consistent with the grouping expression.
What you will learn
- Group by a computed value, e.g. to_char(placed,'YYYY-MM') for monthly totals
- Repeat the grouping expression in SELECT, or point at it with GROUP BY 1
- Predict which rows merge: 'DE' and 'de' collapse once you group by upper(country)
- Read GROUP BY errors as 'this SELECT item is not the grouping expression'
Understanding Grouping on expressions instead of raw columns
GROUP BY does not require a column name; it accepts any expression, and the value that expression produces is the group key. The engine walks the rows that survive WHERE, evaluates the expression once per row, and drops rows that computed the same value into the same bucket. That is why to_char(placed, 'YYYY-MM') collapses six different dates into three rows: the dates themselves were never compared, only the seven-character strings derived from them.
The order the clauses are written is not the order they run: FROM, WHERE, GROUP BY, aggregates, HAVING, SELECT, then ORDER BY. Column aliases are created by SELECT, which happens after grouping, so the portable way to reuse a grouping expression is to write it twice, once in SELECT and once in GROUP BY, while ORDER BY runs last and can simply name the alias. PostgreSQL and MySQL do let GROUP BY refer to an output alias as an extension, but SQL Server and Oracle do not, and a CTE that computes the expression once is the version that runs everywhere.
Because the group key is the computed value, everything else follows: two rows whose raw columns differ can share a group, all rows whose expression yields NULL land in one group together, and any non-aggregate item in SELECT has to be the grouping expression itself rather than the column it was built from. The expression also has to be deterministic, since grouping by random() or by a per-row clock reading gives buckets nobody can reproduce. One practical cost is that an index on placed cannot serve GROUP BY to_char(placed, 'YYYY-MM'), so large tables need an expression index or a stored generated column instead.
-- PostgreSQL
CREATE TABLE orders (
id integer,
placed date,
amount numeric(7,2)
);
INSERT INTO orders VALUES
(1, '2024-01-04', 120.00),
(2, '2024-01-27', 80.50),
(3, '2024-02-02', 200.00),
(4, '2024-02-14', 45.25),
(5, '2024-02-28', 10.00),
(6, '2024-03-09', 300.00);
SELECT to_char(placed, 'YYYY-MM') AS month,
count(*) AS n_orders,
sum(amount) AS revenue
FROM orders
GROUP BY to_char(placed, 'YYYY-MM')
ORDER BY month;The group key is whatever the expression evaluates to, not the column it reads.
Worked examples
CASE as the grouping key
A CASE expression invents labels that do not exist in any column, and those labels become the buckets.
SELECT CASE WHEN amount < 50 THEN 'small'
WHEN amount < 200 THEN 'medium'
ELSE 'large' END AS bucket,
count(*) AS n,
min(amount) AS lo,
max(amount) AS hi
FROM orders
GROUP BY CASE WHEN amount < 50 THEN 'small'
WHEN amount < 200 THEN 'medium'
ELSE 'large' END
ORDER BY lo;Example explained
Line 1The CASE runs once per row before any grouping, turning six amounts into three distinct strings.
Line 2The identical CASE text is repeated in GROUP BY; if the two copies differed at all, PostgreSQL would complain that orders.amount is not in the GROUP BY clause.
Line 3GROUP BY 1 is a shorter equivalent here, because the CASE is the first item in the SELECT list.
Line 4ORDER BY lo sorts on an aggregate, which is legal because ORDER BY is applied after the groups have collapsed.
Numeric buckets from integer division
Arithmetic on a column produces coarser keys, but only if the division is integer division.
CREATE TABLE people (name text, age integer);
INSERT INTO people VALUES
('Ana', 23), ('Bo', 29), ('Cy', 31),
('Di', 35), ('Ed', 47), ('Fi', 52);
SELECT (age / 10) * 10 AS decade,
count(*) AS n_people
FROM people
GROUP BY (age / 10) * 10
ORDER BY decade;Example explained
Line 1age / 10 is integer division because both operands are integers, so 23 and 29 both become 2 and then 20.
Line 2Writing age / 10.0 would yield 2.3 and 2.9, and every distinct age would become its own group.
Line 3The bucket width appears in two places, so widening the bands to five years means changing 10 in both the SELECT and the GROUP BY.
Folding NULL and blank into one named group
Nested functions normalize dirty values so unrelated-looking rows share a bucket, with GROUP BY referring to the expression by position.
CREATE TABLE signups (email text, country text);
INSERT INTO signups VALUES
('a@example.com', 'DE'),
('b@example.com', NULL),
('c@example.com', 'de'),
('d@example.com', ''),
('e@example.com', 'FR');
SELECT coalesce(nullif(upper(trim(country)), ''), 'UNKNOWN') AS region,
count(*) AS n
FROM signups
GROUP BY 1
ORDER BY region;Example explained
Line 1trim and upper are applied per row, so 'DE' and 'de' produce the same key and end up in one bucket.
Line 2nullif turns the empty string into NULL first, so coalesce can catch it; without nullif, '' would sit in its own group beside UNKNOWN.
Line 3GROUP BY 1 points at the first SELECT item by position, so inserting another column before region would silently regroup the query.
Line 4Rows whose expression evaluates to NULL always share a single group; coalesce just gives that group a visible name.
Important notes
GROUP BY on an output alias is a PostgreSQL and MySQL extension, and GROUP BY by position is not supported at all in SQL Server; computing the expression in a CTE and grouping by that column is the form every engine accepts.
An ordinary index on the column cannot satisfy a query grouped by an expression over it, so PostgreSQL needs an expression index such as CREATE INDEX ON orders ((to_char(placed, 'YYYY-MM'))), while other engines use a generated column.
Common mistakes
Selecting the raw column while grouping by an expression over it: PostgreSQL refuses with 'column orders.placed must appear in the GROUP BY clause', and SQLite or MySQL with ONLY_FULL_GROUP_BY disabled quietly returns one arbitrary date per bucket, which is harder to notice.
Grouping by the column but labelling with the expression, as in GROUP BY placed with to_char(placed, 'YYYY-MM') in SELECT: the query is accepted, yet you get one row per date and three separate rows all printed as 2024-02.
Bucketing a decimal column with division: amount / 100 * 100 on numeric keeps the fraction, so 120.00 stays 120.00 and every distinct amount becomes its own group; floor(amount / 100) * 100 is what actually buckets.
Try it yourself
Change, predict, then run
Against the orders table above, write a query grouped by extract(dow from placed) that returns count(*) and sum(amount), then swap the grouping expression for a CASE that returns 'weekend' for dow 0 and 6 and 'weekday' otherwise. Confirm the counts across the new groups still add up to 6.
Open the SQL workspaceCheck your understanding
A visits table holds the city values 'Paris', 'paris' and 'PARIS', one row each. What does SELECT lower(city) AS c, count(*) FROM visits GROUP BY city return in PostgreSQL?
- One row: paris with count 3, because lower(city) merges the three spellings
- An error, because lower(city) does not appear in the GROUP BY clause
- Three rows, each showing paris with count 1, because the grouping key was the raw column
- Three rows showing Paris, paris and PARIS with count 1 each, because SELECT cannot change the labels
Show answer
GROUP BY city compares the raw strings, and those three spellings are three distinct values, so three groups exist before SELECT ever runs; lower() then relabels each group's key, producing three rows that look identical. Option 1 is tempting because the mismatch feels illegal, but PostgreSQL accepts SELECT expressions built out of grouping columns; the error appears in the opposite arrangement, when you group by lower(city) and select city.