Can GROUP BY reference a SELECT-list alias or an ordinal position?

## Why the question comes up A grouped query on a derived value forces you to write the expression twice: ```sql SELECT EXTRACT(YEAR FROM order_date) AS order_year, COUNT(*) AS orders FROM orders GROUP BY EXTRACT(YEAR FROM order_date) ORDER BY order_year; ``` The duplication is annoying and a maintenance hazard — change one copy and the other silently produces a different grouping, or the query fails the every-selected-column check because the two expressions no longer match. The natural instinct is `GROUP BY order_year` or `GROUP BY 1`. ## What the standard permits The logical evaluation order places `GROUP BY` before the select list is computed. At grouping time the query is still working with the input columns of the `FROM` clause; `order_year` is a name that will exist only in the result. So standard `GROUP BY` takes column references and expressions over input columns — no output aliases, and no ordinal positions. `ORDER BY` is the deliberate contrast. Sorting is applied to the finished result, after projection, and the standard explicitly permits both a select-list alias and an ordinal position there. That asymmetry — `ORDER BY order_year` legal, `GROUP BY order_year` not — is the whole point of the question, and it follows from *when* each clause is evaluated rather than from any arbitrary rule. ## What engines actually accept Extensions are common, which is why candidates disagree about the answer: - **PostgreSQL** documents that a `GROUP BY` item may be an input-column name, or the name or ordinal number of an output column; when a name is ambiguous it resolves to the input column. - **MySQL** accepts a select-list alias in `GROUP BY`. - **SQL Server** accepts neither an alias nor an ordinal in `GROUP BY`; you must write the expression. Because the behaviour differs, a query using the alias form is not portable, and one using ordinals has a second problem: `GROUP BY 1, 2` is positional, so reordering or inserting a select-list column silently changes the grouping without any error. Ordinals in `GROUP BY` are best treated as an interactive-analysis convenience, not something you commit. ## Portable ways to avoid writing the expression twice Compute the expression once in an inner query block and group by its name in the outer one. A derived table: ```sql SELECT order_year, COUNT(*) AS orders FROM (SELECT EXTRACT(YEAR FROM order_date) AS order_year FROM orders) t GROUP BY order_year ORDER BY order_year; ``` or the same thing in a `WITH` clause. In the outer block `order_year` is a genuine input column of the derived table, so the reference is standard, no alias extension is involved, and the expression exists in exactly one place. This also satisfies the every-selected-column rule cleanly: the select list projects `order_year`, and `order_year` is the grouping column. ## Matching expressions, not just names One more trap lives here. When you do repeat the expression, engines compare it structurally against the select-list item. `GROUP BY EXTRACT(YEAR FROM order_date)` licenses selecting exactly that expression. Selecting `EXTRACT(YEAR FROM order_date) + 0`, or a differently-cast variant, is a *different* expression and can be rejected as not a grouping expression even though it is deterministic in the group. Keep the two copies textually identical, or move to the derived-table form where the question cannot arise. ## How to answer in an interview Lead with the reason, not the verdict: grouping precedes projection, so the alias does not yet exist; that is why the standard requires the expression and why `ORDER BY` — which runs after projection — is allowed the alias. Then note that several engines relax it, that ordinals are positional and fragile, and that the derived table or CTE is the portable way to write the expression once. That sequence shows you understand the evaluation model rather than having memorized which database tolerates what.