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

Question

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

This has nothing to do with aliasing, the subquery in your first query is invalid because you select a column not in an aggregate function, nor in a group by. — HoneyBadger
– HoneyBadger, Commented Jun 23, 2022 at 14:06
Probably a matter of opinion, but I think the second query is much easier to read (even if the first were correct) — HoneyBadger
– HoneyBadger, Commented Jun 23, 2022 at 14:08

topsail · Accepted Answer · 2022-06-23 17:12:57Z

2

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.

edited Jun 23, 2022 at 17:12

answered Jun 23, 2022 at 14:20

topsail

3,2003 gold badges23 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Charlieface Over a year ago

ORDER BY actually is allowed to use the SELECT alias

HoneyBadger Over a year ago

Even though year isn't a reserved keyword, I would discourage its use as alias. startYear, for example, would be more descriptive and less likely to cause confusion (if only because of syntax highlighting)

topsail Over a year ago

I have updated the example to use the select alias in the order by clause. Probably I would also avoid year as a column name generally as well.

Shmiel · Accepted Answer · 2022-06-23 14:19:13Z

0

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.

edited Jun 23, 2022 at 14:19

answered Jun 23, 2022 at 14:08

Shmiel

1,27713 silver badges30 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related