Alias for GROUP BY clause?

Question

In MySQL, it is possible to use an alias in GROUP BY clause that has been created in SELECT clause (Cf. MySQL Reference).

I am just wondering why it is not possible to create an alias in GROUP BY and use it in SELECT, which follows the execution order of SELECT statement.

In other words, why is the following ungrammatical?

SELECT region, SUM(population)
  FROM population_us_states
 GROUP BY
       CASE state_name
               WHEN 'CT' THEN 'New England'
               WHEN 'RI' THEN 'New England'
               WHEN 'MA' THEN 'New England'
               WHEN 'ME' THEN 'New England'
               WHEN 'NH' THEN 'New England'
               WHEN 'VT' THEN 'New England'
               WHEN 'CA' THEN 'West Coast'
               WHEN 'OR' THEN 'West Coast'
               WHEN 'WA' THEN 'West Coast'
        ELSE 'other' END AS region;

For better maintainability, this really ought to be in its own table. It _may_ grant better performance, despite the extra join (supposedly grouping by a case this way can play havok with optimizers) — Clockwork-Muse, Nov 29 '11 at 16:50

score 5 · Accepted Answer · answered Nov 29 '11 at 14:46

5

Use a nested SELECT - the inner one to extract the population and generate the calculated column, and the outer one can then GROUP BY that column:

SELECT region, SUM(population) FROM (

   SELECT CASE ...

   ... AS region, population FROM population_us_states )

GROUP BY region

answered Nov 29 '11 at 14:46

Alnitak

334,560
70
407
495

@amemus: Your question should be addressed to the SQL-standards commitee, I think :) – ypercubeᵀᴹ Nov 29 '11 at 14:55
On a second thought (after my deleted comment), this is probably the cleverest workaround available right now. Sticking to the current ANSI means that I have to write two identical CASE expressions both in SELECT and GROUP BY. Thanks, Alnitak. I don't think the SQL committee would take time to answer me if I asked the question but I am just curious.... – amemus Nov 29 '11 at 15:04

score 1 · Answer 2 · 2011-11-29T15:58:22.693

It is because the group by refers to the main clause, rather than vice versa (or both - the relationship between the two is not recursive).

You can do this in MySQL without using a sub-query, but you have to group by a value declared in your main select - so your initial query is not valid, but the following should be:

SELECT CASE state_name
               WHEN 'CT' THEN 'New England'
               WHEN 'RI' THEN 'New England'
               WHEN 'MA' THEN 'New England'
               WHEN 'ME' THEN 'New England'
               WHEN 'NH' THEN 'New England'
               WHEN 'VT' THEN 'New England'
               WHEN 'CA' THEN 'West Coast'
               WHEN 'OR' THEN 'West Coast'
               WHEN 'WA' THEN 'West Coast'
        ELSE 'other' END AS region,
        SUM(population)
  FROM population_us_states
 GROUP BY
       region;

score 0 · Answer 3 · answered Jun 25 '20 at 08:03

Your alias name should contain single or double quotes for MySQL to work like so

SELECT CASE state_name
               WHEN 'CT' THEN 'New England'
               WHEN 'RI' THEN 'New England'
               WHEN 'MA' THEN 'New England'
               WHEN 'ME' THEN 'New England'
               WHEN 'NH' THEN 'New England'
               WHEN 'VT' THEN 'New England'
               WHEN 'CA' THEN 'West Coast'
               WHEN 'OR' THEN 'West Coast'
               WHEN 'WA' THEN 'West Coast'
        ELSE 'other' END AS 'region',
        SUM(population)
  FROM population_us_states
 GROUP BY
       'region';

Alias for GROUP BY clause?

3 Answers3

Linked