SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Need to group by an alias.... Help with sub query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

qman
Constraint Violating Yak Guru

USA
439 Posts

Posted - 01/17/2013 :  13:34:44  Show Profile  Reply with Quote
I have the following simple select, where I am summing a volume and grouping by a product name.... Simple enough.....

SELECT p.product_name, SUM(s.vol) AS ytd_vol
FROM sales s
left outer join product p on p.productID = s.productID
WHERE p.product_name LIKE '%test%'
GROUP BY p.product_name

I would like to introduce some case logic, and then alias the case statement, and use it in the grouping. Research shows that this is a SQL no no... due to sql ordering when executing select statements.

Can someone help me out with a sub query which will accomplish the same thing as below?

Thanks in adavnce!

SELECT case
when p.status = 'Inactive' then p.old_product_name
else p.product_name end as TEST_NAME,
SUM(s.vol) AS ytd_vol
FROM sales s
left outer join product p on p.productID = s.productID
WHERE p.product_name LIKE '%test%'
GROUP BY TEST_NAME

James K
Flowing Fount of Yak Knowledge

3585 Posts

Posted - 01/17/2013 :  13:39:44  Show Profile  Reply with Quote
Instead of using the alias, use the case expression in the group by clause
SELECT case
when p.status = 'Inactive' then p.old_product_name
else p.product_name end as TEST_NAME,
SUM(s.vol) AS ytd_vol
FROM sales s
left outer join product p on p.productID = s.productID
WHERE p.product_name LIKE '%test%'
GROUP BY case
when p.status = 'Inactive' then p.old_product_name
else p.product_name end
Go to Top of Page

qman
Constraint Violating Yak Guru

USA
439 Posts

Posted - 01/17/2013 :  13:49:51  Show Profile  Reply with Quote
Intersting... My results differ when using the case statement in the group by, compared to the original query....
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/17/2013 :  14:33:06  Show Profile  Reply with Quote
You can put inside subquery and use alias name.
Go to Top of Page

qman
Constraint Violating Yak Guru

USA
439 Posts

Posted - 01/17/2013 :  14:36:37  Show Profile  Reply with Quote
I understand the group by version, would be nice to see a sub query version in order to compare the two.

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3585 Posts

Posted - 01/17/2013 :  17:50:51  Show Profile  Reply with Quote
I think what sodeep meant is something like shown below
SELECT TEST_NAME, SUM(s.vol) AS ytd_vol
FROM
(
SELECT case
when p.status = 'Inactive' then p.old_product_name
else p.product_name end as TEST_NAME,
s.vol
FROM sales s
left outer join product p on p.productID = s.productID
WHERE p.product_name LIKE '%test%'
) s
GROUP BY TEST_NAME;
Before you decide to use this approach enable show plan (control-M) and compare the relative costs.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3585 Posts

Posted - 01/17/2013 :  17:52:39  Show Profile  Reply with Quote
quote:
Originally posted by qman

Intersting... My results differ when using the case statement in the group by, compared to the original query....

One would expect them to be different, wouldn't you? In one case you are grouping by product_name, in the second case you are grouping by a different entity - a composite of product_name and old_product_name. So there may be a larger or fewer number of rows in the composite grouping case.
Go to Top of Page

qman
Constraint Violating Yak Guru

USA
439 Posts

Posted - 01/18/2013 :  09:22:06  Show Profile  Reply with Quote
Thanks to all, SQLTeam is a great resource!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000