SQL – Why doesn't using alias in Group By doesn't work in SQL server

June 23, 2022

Why can’t I use Alias even after nesting the code, it seems to not work, I used a different approach which works but doesn’t look good.

This doesn’t work

SELECT Year,
       Number_of_rides
FROM   (SELECT DATEPART(YEAR, [starttime]) AS Year,
               COUNT (*)                   AS Number_of_rides
        FROM   [dbo].[Citi-Bike-Trip-Data]) AS x
GROUP  BY x.Year,
          x.Number_of_rides
ORDER  BY Year ASC

This works

SELECT 
     DATEPART(YEAR, 
      [starttime]) AS Year,
      COUNT (*) AS Number_of_rides 
FROM 
    [dbo].[Citi-Bike-Trip-Data]
GROUP BY DATEPART(YEAR, [starttime])

>Solution :

As noted by others, the problem is not with the alias. You are missing group by in the subquery, which is required since you are using count there:

SELECT Year,
       Number_of_rides
FROM   (SELECT DATEPART(YEAR, [starttime]) AS Year,
               COUNT (*)                   AS Number_of_rides
        FROM   [dbo].[Citi-Bike-Trip-Data]
        GROUP BY DATEPART(YEAR, [starttime])) AS x
GROUP  BY x.Year,
          x.Number_of_rides
ORDER  BY Year ASC

Since the data is already grouped in the subquery, you don’t need to group again:

SELECT Year,
       Number_of_rides
FROM   (SELECT DATEPART(YEAR, [starttime]) AS Year,
               COUNT (*)                   AS Number_of_rides
        FROM   [dbo].[Citi-Bike-Trip-Data]
        GROUP BY DATEPART(YEAR, [starttime])) AS x
ORDER  BY Year ASC

And since the data is already grouped and already exactly what you want, you really don’t need to select it again, so:

SELECT DATEPART(YEAR, [starttime]) AS Year,
       COUNT (*)                   AS Number_of_rides
FROM   [dbo].[Citi-Bike-Trip-Data]
GROUP BY DATEPART(YEAR, [starttime])
ORDER BY DATEPART(YEAR, [starttime])

Which is almost like your original second version, but only adding in the order by.