Can you GROUP BY a derived expression or a SELECT alias, and how does this differ across databases?

How to think about it

This is another logical execution order question wearing a different hat. The fact it rests on: GROUP BY is evaluated before SELECT, so a SELECT alias doesn’t exist yet when GROUP BY runs — at least in the engines that follow the standard strictly.

Grouping by an expression — always works

Write the expression out fully in GROUP BY and it works everywhere, no exceptions. Here we collapse daily orders into months by slicing the date string:

SELECT substr(order_date, 1, 7) AS month,
       SUM(revenue) AS total_revenue,
       COUNT(*)     AS order_count
FROM orders
GROUP BY substr(order_date, 1, 7)   -- the expression repeated, not the alias
ORDER BY month;

month	total_revenue	order_count
2024-01	250	2
2024-02	450	2
2024-03	480	2

Three months, two orders each, revenue summed within each — and it runs identically on PostgreSQL, MySQL, BigQuery, and SQL Server because nothing depends on alias resolution.

Grouping by a SELECT alias — engine-dependent

This is where portability breaks. Lenient engines let you name the alias in GROUP BY; strict ones reject it, because at GROUP BY time the alias hasn’t been defined yet:

-- Works in MySQL and BigQuery; ERRORS in PostgreSQL and SQL Server:
SELECT substr(order_date, 1, 7) AS month, SUM(revenue)
FROM orders
GROUP BY month;        -- PostgreSQL: column "month" does not exist

The portable fix is to repeat the expression (as above) or push it into a subquery so the alias is a real column by the time you group:

SELECT month, SUM(revenue)
FROM (
  SELECT substr(order_date, 1, 7) AS month, revenue FROM orders
) sub
GROUP BY month;        -- now 'month' is a genuine column, portable everywhere

Grouping by column position — avoid it

GROUP BY 1 means “group by the first SELECT column.” Most engines accept it, but it’s brittle: reorder your SELECT list and the grouping changes silently, with no error to catch it.

-- 'GROUP BY 1' is GROUP BY country today; add a column before it and it breaks quietly
SELECT country, SUM(revenue) FROM orders GROUP BY 1;