Question about alias' and Derived Table

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)

Question about alias' and Derived Table

Author

Topic

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-20 : 11:30:23

I have a situation similar to the following...please excuse the lame example I used, but it outlines my needs...


Declare @t1 table (name varchar(20) , Ind_Money int)
Insert @t1
Select 'Derek', 1 UNION
Select 'Dave', 2 Union
Select 'Dave', 3 Union
Select 'Fred', 8 Union
Select 'Fred', 4 

Declare @t2 table (Group_name varchar(20) , Group_Money int)
Insert @t2
Select 'D Group', 50 UNION
Select 'D Group', 20 UNION
Select 'Other Group', 30  

--1st query to show initial setup

Select 
	Case 
		when Name IN ('Derek', 'Dave') then 'D Group' else 'Other Group'
	End as Groups
	, Sum(Ind_Money) as CollectedMoney
From @t1 a
Group by 
	Case 
		when Name IN ('Derek', 'Dave') then 'D Group' else 'Other Group'
	End

--2nd Query which adds the new summed 3rd column 
Select 
	Case 
		when Name IN ('Derek', 'Dave') then 'D Group' else 'Other Group'
	End as Groups
	, Sum(Ind_Money) as CollectedMoney
	, c.Total_Group_Money
From @t1 a
Left outer join
(
	Select
		Group_Name
		,Sum(Group_Money) as Total_Group_Money
	From @t2
	Group by Group_Name
) c ON (Case when Name IN ('Derek', 'Dave') then 'D Group' else 'Other Group' End)  = c.Group_Name
Group by 
	Case 
		when Name IN ('Derek', 'Dave') then 'D Group' else 'Other Group'
	End
	, c.Total_Group_Money

What I am looking to avoid, for simplicity's sake, and also to avoid errors, it to be able to take the ON statement of the Left Join and call it by it's alias.

IE:
Instead of...

ON (Case when Name IN ('Derek', 'Dave') then 'D Group' else 'Other Group' End)  = c.Group_Name

I want to be able to do...

a.Groups = c.Group_Name

Does that make sense? Basically when does an alias become reference-able?

Thanks!

EDIT: I know this can be simplified by using a Temporary table and then joining to that, I was trying to avoid creating temps and getting it all in 1 go round. I believe a CTE would work in a similar fashion, but can it be accomplished otherwise?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-20 : 11:45:21

Sure. Just make your select from @t1 also a derived table.

No, you're never too old to Yak'n'Roll if you're too young to die.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-20 : 12:05:45

Ah ok, thanks appreciate it, and understand what you mean.

So in relation to my last question, when does a column name become able to be referenced?

Since I know I can not do:

Select
Case
when Name IN ('Derek', 'Dave') then 'D Group' else 'Other Group'
End as Groups
, Sum(Ind_Money) as CollectedMoney
From @t1 a
Group by
Groups -- <Produces the error

And this is equivalent to also not being able to do:

Select
Groups =(Case
when Name IN ('Derek', 'Dave') then 'D Group' else 'Other Group'
End)
, Sum(Ind_Money) as CollectedMoney
From @t1 a
Group by
Groups

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-20 : 12:20:51

Please read this, I think that I am not able to explain it better in the english language:
http://databases.aspfaq.com/database/how-do-i-use-a-select-list-alias-in-the-where-or-group-by-clause.html

See also my picture:

No, you're never too old to Yak'n'Roll if you're too young to die.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-20 : 12:41:24

Thanks a ton, this is very useful information.

One last question, In the article you supplied it states:

From -> Where -> Group By -> Having -> SELECT

This would explain why alias can not be used in the Where/Group By clause. But in your diagram it has the Select statement BEFORE the 'Grouping and Aggregation' step, which might lead people to believe the alias exists before grouping (before the group by clause.)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-20 : 13:05:47

I agree.
I have made this picture using paint based on a picture coming from the book:
Microsoft Official Workshop - 2784A - Tuning and Optimizing Queries
Using Microsoft SQL Server 2005 - Resource Toolkit

Looks like in case of grouping (the second row in the picture) the symbol "SELECT" is missing before ORDER BY.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources