Issue with Case Statement 1

[luvtitans50]

I am trying to perform the following calculation:
sum(CASE when reporttype = 'Margin' and BillingType = 'Staffing' then PlanMTD END)/
sum(CASE when reporttype = 'Billing' and BillingType = 'Staffing' then PlanMTD END)*100

and I am getting "Divide by zero error"

How can go about avoiding that error. I understand what is causing this error but not sure how to write the code to get around it.

Thanks a million!!!

 

[bborissov]

sum(CASE when reporttype  = 'Margin' and
              BillingType = 'Staffing' 
         then PlanMTD END) /
    sum(CASE when reporttype  = 'Billing'  and 
                  BillingType = 'Staffing' and
                  PlanMTD <> 0
             then PlanMTD END)*100

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

Boris.... what is there are multiple values in the result set that sum up to 0?

try this:

    Coalesce(sum(CASE when reporttype  = 'Margin' and
              BillingType = 'Staffing'
         then PlanMTD END) /
    NullIf(sum(CASE when reporttype  = 'Billing'  and
                  BillingType = 'Staffing' and
             then PlanMTD END)*100, 0), 0)

This assumes you want the output to be 0 then the denominator is 0.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

George - beat me up again - I was just going to suggest the usual trick with NULLIF.

 

[luvtitans50]

Perfect gmmastros, THANKS!!!

 

[bborissov]

It depends what you want
I personally prefer NULL as a result

[COLOR=blue]DECLARE[/color] @Test [COLOR=blue]TABLE[/color] (Fld1 [COLOR=blue]Int[/color], Fld2 [COLOR=blue]int[/color], Fld3 [COLOR=blue]Char[/color](1))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](1, 0,[COLOR=red]'A'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](1, 0,[COLOR=red]'B'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](1, 0,[COLOR=red]'A'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](1, 0,[COLOR=red]'B'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](1, 0,[COLOR=red]'A'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](1, 0,[COLOR=red]'B'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](1, 0,[COLOR=red]'A'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](1, 0,[COLOR=red]'B'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](1, 0,[COLOR=red]'A'[/color])


[COLOR=blue]SELECT[/color] sum([COLOR=blue]CASE[/color] [COLOR=blue]when[/color] Fld3 = [COLOR=red]'A'[/color]
               [COLOR=blue]then[/color] Fld1 [COLOR=blue]END[/color]) /
       sum([COLOR=blue]CASE[/color] [COLOR=blue]when[/color] Fld3 = [COLOR=red]'A'[/color] AND Fld2 <> 0
                [COLOR=blue]then[/color] Fld2 [COLOR=blue]END[/color])*100
[COLOR=blue]FROM[/color] @test

That gives me what I want - NULL. Sure if you want Zero as a result it is easy to use COALESCE or ISNULL

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

Boris,

Try adding this to your data:

[tt]
INSERT INTO @Test VALUES(1, -1,'A')
INSERT INTO @Test VALUES(1, 1,'A')
[/tt]

You'll still get the divide by zero error.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

O!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
O(O)!!!!
Many more "O!"s here!
That didn't come to my mind :-(
Grrrr!
I want to bite my ... (not for a forum posting)!


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

I know. I know. Take it from me.... that would be a pretty nasty bug to find.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom