SQL - using alias in Group By

Haoest_10088 0
Viewed 218348

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?

alias group-by sql
10 Answers

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.

user_324530
Related