Truncate year, group by year and count another column in SQL-CodePudding

I want to use the following sql code to get Year information from MyDate and count unique CustomerId but get error.

SELECT YEAR(MyDate) AS MyYear, COUNT(DISTINCT CustomerId) AS MyCustomerCount

FROM MyTable

GROUP BY MyYear

However the following code works.

SELECT YEAR(MyDate), COUNT(DISTINCT CustomerId)

FROM MyTable

GROUP BY YEAR(MyDate)

Why can't alias be used in this scenario?

CodePudding user response：

Simply because this is how it was defined in SQL standard.

Syntax rules

Group by clause is defined as:

However Order by clause is defined as bellow

So here you can see alias exists after order by clause then we can use it normally but not after group by clause.