Need to group by an alias.... Help with sub query

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)

Need to group by an alias.... Help with sub query

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2013-01-17 : 13:34:44

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-17 : 13:39:44

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

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2013-01-17 : 13:49:51

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2013-01-17 : 14:33:06

You can put inside subquery and use alias name.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2013-01-17 : 14:36:37

I understand the group by version, would be nice to see a sub query version in order to compare the two.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-17 : 17:50:51

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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-17 : 17:52:39

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.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2013-01-18 : 09:22:06

Thanks to all, SQLTeam is a great resource!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources