Referencing aliased columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Referencing aliased columns

Author

Topic

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-06-17 : 12:27:31

Hi there,

I'm trying to calculate a sort of rating:

select Player,
    sum(case when Event = 'S' then 1.0 else 0.0 end) as Success,
    sum(case when Event in ('S', 'F') then 1.0 else 0.0 end) as Attempts,
    case when Attempts > 0 then Success / Attempts else NULL end as Rating
from EventList
group by Player

It says comlumn names are invalid. Any idea?

Thanks,
Bjoern

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-17 : 12:30:05

[code]select Player,
sum(case when Event = 'S' then 1.0 else 0.0 end) as Success,
sum(case when Event in ('S', 'F') then 1.0 else 0.0 end) as Attempts,
case when sum(case when Event in ('S', 'F') then 1.0 else 0.0 end) > 0 then 'Success / Attempts' else NULL end as Rating
from EventList
group by Player[/code]
you cant use aliases in expressions. you have to repeat entire expression

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-06-17 : 12:38:43

Thanks visakh,


    case when sum(case when Result in ('S', 'F') then 1.0 else 0.0 end) > 0 
        then sum(case when Result = 'S' then 1.0 else 0.0 end) 
        / sum(case when Result in ('S', 'F') then 1.0 else 0.0 end) else NULL end as Rating

works for me. Is there any reason that we have to repeat the whole expression? It doesn't make the code more readable, does it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-17 : 12:47:57

quote:
Originally posted by bjoerns

Thanks visakh,

case when sum(case when Result in ('S', 'F') then 1.0 else 0.0 end) > 0
then sum(case when Result = 'S' then 1.0 else 0.0 end)
/ sum(case when Result in ('S', 'F') then 1.0 else 0.0 end) else NULL end as Rating

works for me. Is there any reason that we have to repeat the whole expression? It doesn't make the code more readable, does it?

Because the alias was created on same level. However, if you want to use alias in expression then you should do like this

select Player,Success,Attempts,
case when Attempts > 0 then 'Success / Attempts' else NULL end as Rating
from
(
select Player,
    sum(case when Event = 'S' then 1.0 else 0.0 end) as Success,
    sum(case when Event in ('S', 'F') then 1.0 else 0.0 end) as Attempts    
from EventList
group by Player)t

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-17 : 13:09:34

Use Derived table. Thats more readable and easy to write
Also note that you cant use alias name directly except in Order by clause

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources