CODEWITHSUNDEEP
mysqlsql
amemus
amemus

Reputation: 363

Alias for GROUP BY clause?

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;

Upvotes: 7

Views: 9964

Answers (3)

Jose Mhlanga
Jose Mhlanga

Reputation: 855

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';

Upvotes: 0

user359040
user359040

Reputation:

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;

Upvotes: 1

Alnitak
Alnitak

Reputation: 339806

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

Upvotes: 5

Related Questions