SQL - using an alias in a By group

Question

Just interested in SQL syntax. Therefore if i have

SELECT itemName as ItemName, substring(itemName, 1,1) as FirstLetter, Count(itemName) FROM table1 GROUP BY itemName, FirstLetter 

This is not true because

 GROUP BY itemName, FirstLetter 

really should be

 GROUP BY itemName, substring(itemName, 1,1) 

But why can't we just use the former for convenience?

Answer 1

SQL is implemented as if the query was executed in the following order:

• FROM clause
• WHERE clause
• GROUP BY clause
• HAVING offer
• SELECT clause
• ORDER BY clause

For most relational database systems, this order explains which names (columns or aliases) are valid because they should have been entered in the previous step.

So, in Oracle and SQL Server, you cannot use the term in the GROUP BY clause, which you define in the SELECT clause, because GROUP BY is executed before the SELECT clause.

There are exceptions: MySQL and Postgres seem to have the extra skill that allows this.

Answer 2

You can always use a subquery to use an alias; Of course, check the performance (perhaps the db server will work the same, but it is never afraid to check):

 SELECT ItemName, FirstLetter, COUNT(ItemName) FROM ( SELECT ItemName, SUBSTRING(ItemName, 1, 1) AS FirstLetter FROM table1 ) ItemNames GROUP BY ItemName, FirstLetter 

Answer 3

At least in PostgreSQL, you can use the column number in the result set in your GROUP BY clause:

 SELECT itemName as ItemName, substring(itemName, 1,1) as FirstLetter, Count(itemName) FROM table1 GROUP BY 1, 2 

Of course, it gets painful if you do it online and you edit the query to change the number or order of columns as a result. But still.

Answer 4

SQL Server does not allow an alias in a GROUP BY clause because of the logical processing order. The GROUP BY clause is processed before the SELECT clause, so the alias is not known when the GROUP BY clause is evaluated. This also explains why you can use an alias in the ORDER BY clause.

Here is one source of information about SQL Server logical processing steps .

Answer 5

Caution that using an alias in a By group (for services that support it, such as postgres) may have unintended results. For example, if you create an alias that already exists in an internal expression, Group By will select the name of the internal field.

 -- Working example in postgres select col1 as col1_1, avg(col3) as col2_1 from (select gender as col1, maritalstatus as col2, yearlyincome as col3 from customer) as layer_1 group by col1_1; -- Failing example in postgres select col2 as col1, avg(col3) from (select gender as col1, maritalstatus as col2, yearlyincome as col3 from customer) as layer_1 group by col1; 

Answer 6

Some DBMSs allow you to use an alias instead of repeating the entire expression.
One such example is Teradata.

I avoid the designation of ordinal positions recommended by Bill for the reasons described in this SO question .

An easy and reliable alternative is to always repeat the expression in the GROUP BY clause.
DRY does not apply to SQL.

Answer 7

Beware of using aliases when grouping results from a view in SQLite. You will get unexpected results if the alias name matches the column name of all base tables (to views.)

Answer 8

That same day, I discovered that Rdb, a former DEC product supported by Oracle, allowed the use of a column alias in GROUP BY. Mainstream Oracle through version 11 does not allow the use of column aliases in GROUP BY. Not sure if Postgresql, SQL Server, MySQL, etc. Will or will not allow it. YMMV.

Answer 9

I do not answer why this is so, but I just wanted to show a way to limit this restriction in SQL Server using CROSS APPLY to create an alias. Then you use it in the GROUP BY , for example:

 SELECT itemName as ItemName, FirstLetter, Count(itemName) FROM table1 CROSS APPLY (SELECT substring(itemName, 1,1) as FirstLetter) Alias GROUP BY itemName, FirstLetter