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 2000 Forums
 Transact-SQL (2000)
 Case within a Case

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_type
End 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',90

select
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 brand
from @test
group by
brand,
product_type

select
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 brand
from @test
group by
brand,
product_type

select
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 brand
from @test
group by
brand,
product_type

select
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 brand
from @test
group by
brand,
product_type



MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

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 50
Pepsi 80 40

I 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.
Go to Top of Page

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',90


select 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) Others
from @test
group by Brand
order by brand[/code]

Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -