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.
Author |
Topic |
Dave_Fergy
Starting Member
5 Posts |
Posted - 2006-11-17 : 17:50:07
|
Can you do a case within a case? I am trying to do something like the code below. Am I on the right track or is there another way to accomplish this task.i.e.Case Brand when Pepsi Then Case Product_Type When Can Then Sum (Total_sold) When Bottles Then Sum (Total_Sold) End as Product_type When Coke Then Case Product_Type When Can Then Sum (Total_sold) When Bottles Then Sum (Total_Sold) End as Product_typeEnd as Brand |
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2006-11-17 : 18:59:41
|
You can definitely imbed CASE statements; however, you really need to think about what you are doing. Write out the CASE statements as formulas and give an English narrative to them. It will help you logically think through the results. Here's what your example would give you (makes no sense at all):declare @test table( brand nvarchar(55), product_type nvarchar(55), total_sold int)insert @test(brand,product_type,total_sold) select 'pepsi','can',10 union all select 'pepsi','bottle',10 union all select 'pepsi','bottle',2 union all select 'pepsi','can',5 union all select 'coke','can',10 union all select 'pepsi','bottle',10 union all select 'pepsi','bottle',8 union all select 'pepsi','can',9 union all select 'coffee','car',100 union all select 'coffee','jug',90select Case brand when 'Pepsi' Then Case product_type When 'Can' Then Sum (total_sold) When 'Bottle' Then Sum (total_Sold) End When 'Coke' Then Case product_type When 'Can' Then Sum (total_sold) When 'Bottle' Then Sum (total_Sold) End End as brandfrom @testgroup by brand, product_typeselect brand as real_brand, Case brand when 'Pepsi' Then Case product_type When 'Can' Then Sum (total_sold) When 'Bottle' Then Sum (total_Sold) End When 'Coke' Then Case product_type When 'Can' Then Sum (total_sold) When 'Bottle' Then Sum (total_Sold) End End as brandfrom @testgroup by brand, product_typeselect Case brand when 'Pepsi' Then Case product_type When 'Can' Then Sum (total_sold) When 'Bottle' Then Sum (total_Sold) End When 'Coke' Then Case product_type When 'Can' Then Sum (total_sold) When 'Bottle' Then Sum (total_Sold) End End as brandfrom @testgroup by brand, product_typeselect brand as real_brand, product_type, Case brand when 'Pepsi' Then Case product_type When 'Can' Then Sum (total_sold) When 'Bottle' Then Sum (total_Sold) End When 'Coke' Then Case product_type When 'Can' Then Sum (total_sold) When 'Bottle' Then Sum (total_Sold) End End as brandfrom @testgroup by brand, product_type MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
Dave_Fergy
Starting Member
5 Posts |
Posted - 2006-11-20 : 09:55:28
|
I might be going about this the wrong way. What I am trying to do is get the information by each specific brand.Brand Bottles Cans Coke 100 50Pepsi 80 40I want to sum the different package categories by each Brand and i thought if I did a Case when brand do x and then inside that when bottles sum them, when cans sum then and so on. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-20 : 10:07:33
|
[code]declare @test table( brand nvarchar(55), product_type nvarchar(55), total_sold int)insert @test(brand,product_type,total_sold) select 'pepsi','can',10 union all select 'pepsi','bottle',10 union all select 'pepsi','bottle',2 union all select 'pepsi','can',5 union all select 'coke','can',10 union all select 'pepsi','bottle',10 union all select 'pepsi','bottle',8 union all select 'pepsi','can',9 union all select 'coffee','car',100 union all select 'coffee','jug',90select Brand, sum(case when product_type = 'bottle' then total_sold else 0 end) Bottles, sum(case when product_type = 'can' then total_sold else 0 end) Cans, sum(case when product_type = 'jug' then total_sold else 0 end) Cans, sum(case when product_type not in ('bottle', 'can', 'jug') then total_sold else 0 end) Othersfrom @testgroup by Brandorder by brand[/code]Peter LarssonHelsingborg, Sweden |
|
|
|
|
|
|
|