CODEWITHSUNDEEP
sqlgroup-byalias
Haoest
Haoest

Reputation: 13896

SQL - using alias in Group By

Just curious about SQL syntax. So if I have

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

This would be incorrect because 

GROUP BY itemName, FirstLetter

really should be 

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

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

Upvotes: 213

Views: 269560

Answers (12)

Lenival Gomes
Lenival Gomes

Reputation: 1

Oracle 23c will bring this feature. For now it is not possible.

Upvotes: 0

Seeker
Seeker

Reputation: 1268

Use backtick character ( ` )

This works for me in MySQL (it can also be used in HAVING clause):

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

Upvotes: 0

rogerdpack
rogerdpack

Reputation: 66741

In at least Postgres, you can use the alias name in the group by clause:

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

I wouldn't recommend renaming an alias as a change in capitalization, that causes confusion.

Upvotes: 0

Ricardo
Ricardo

Reputation: 808

I'm not answering why it is so, but only wanted to show a way around that limitation in SQL Server by using CROSS APPLY to create the alias. You then use it in the GROUP BY clause, like so:

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

Upvotes: 17

Shannon S
Shannon S

Reputation: 191

Caution that using alias in the Group By (for services that support it, such as postgres) can have unintended results. For example, if you create an alias that already exists in the inner statement, the Group By will chose the inner field name.

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

Upvotes: 6

Codo
Codo

Reputation: 78825

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

  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. ORDER BY clause

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

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

There are exceptions though: MySQL and Postgres seem to have additional smartness that allows it.

Upvotes: 411

GGGforce
GGGforce

Reputation: 653

Beware of using aliases when grouping the results from a view in SQLite. You will get unexpected results if the alias name is the same as the column name of any underlying tables (to the views.)

Upvotes: 2

bobs
bobs

Reputation: 22184

SQL Server doesn't allow you to reference the alias in the GROUP BY clause because of the logical order of processing. 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 the alias in the ORDER BY clause.

Here is one source for information on the SQL Server logical processing phases.

Upvotes: 16

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Back in the day I found that Rdb, the former DEC product now supported by Oracle allowed the column alias to be used in the GROUP BY. Mainstream Oracle through version 11 does not allow the column alias to be used in the GROUP BY. Not sure what Postgresql, SQL Server, MySQL, etc will or won't allow. YMMV.

Upvotes: 0

Chris Shaffer
Chris Shaffer

Reputation: 32575

You could always use a subquery so you can use the alias; Of course, check the performance (Possible the db server will run both the same, but never hurts to verify):

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

Upvotes: 36

mechanical_meat
mechanical_meat

Reputation: 169284

Some DBMSs will let you use an alias instead of having to repeat the entire expression.
Teradata is one such example.

I avoid ordinal position notation as recommended by Bill for reasons documented in this SO question.

The easy and robust alternative is to always repeat the expression in the GROUP BY clause.
DRY does NOT apply to SQL.

Upvotes: 5

Bill Gribble
Bill Gribble

Reputation: 1797

At least in PostgreSQL you can use the column number in the resultset 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 this starts to be a pain if you are doing this interactively and you edit the query to change the number or order of columns in the result. But still.

Upvotes: 26

Related Questions