| Author |
 Topic  |
|
|
qman
Constraint Violating Yak Guru
USA
422 Posts |
 Posted - 01/17/2013 : 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
Flowing Fount of Yak Knowledge
1481 Posts |
Posted - 01/17/2013 : 13:39:44
|
Instead of using the alias, use the case expression in the group by clauseSELECT 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
USA
422 Posts |
Posted - 01/17/2013 : 13:49:51
|
| Intersting... My results differ when using the case statement in the group by, compared to the original query.... |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 01/17/2013 : 14:33:06
|
| You can put inside subquery and use alias name. |
|
|
|
qman
Constraint Violating Yak Guru
USA
422 Posts |
Posted - 01/17/2013 : 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
Flowing Fount of Yak Knowledge
1481 Posts |
Posted - 01/17/2013 : 17:50:51
|
I think what sodeep meant is something like shown belowSELECT 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
Flowing Fount of Yak Knowledge
1481 Posts |
Posted - 01/17/2013 : 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
USA
422 Posts |
Posted - 01/18/2013 : 09:22:06
|
| Thanks to all, SQLTeam is a great resource! |
|
|
| |
Topic |
|
Need to group by an alias.... Help with sub query
