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

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])

CodePudding user response：

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 [Year]

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

CodePudding user response：

The problem is you need to group by in your subquery, so use this:

SELECT datepart(year, x.starttime) as Year, x.Number_of_rides 
from 
(
    select [starttime], COUNT (*) AS Number_of_rides 
    FROM [dbo].[Citi-Bike-Trip-Data]
    GROUP BY [starttime]
) as x
Order by x.[starttime] ASC

Only select the columns in your subquery, and then use datepart(year, x.starttime) in your outer query would be a good solution.

Or a cte would do same thing.

If you need to group by year, then the only solution I can think of is using group by datepart(year, starttime) as your second query, as you can only use computed column names in order by clause, but not in where or group by.