# Thread: SQL CASE statement

1. Registered User
Join Date
Nov 2003
Posts
10

## Unanswered: SQL CASE statement

Hi Ive got a simple query where I want to calculate an average of one number divided by the other ie: avg(x / y)

Im trying to use a case statement to return 0 in the event that y is 0, to avoid a division by zero error. My query is still returning a division by zero error anyway can anybody help?

SELECT CCode, CASE WHEN BS_TOTAL_ASSETS = 0 THEN 0 ELSE AVG(BSCLTradeCreditors / BS_TOTAL_ASSETS) END AS myaverage
FROM [Company/Year]
GROUP BY CCode, BS_TOTAL_ASSETS

Thanks

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
try this (untested) --

SELECT CCode
, AVG(CASE WHEN BS_TOTAL_ASSETS = 0 THEN 0 ELSE BSCLTradeCreditors / BS_TOTAL_ASSETS END) AS myaverage
FROM [Company/Year]
GROUP BY CCode

3. Registered User
Join Date
Nov 2003
Posts
10

## Perfect

Thanks, your code worksed like a charm. I just needed to cast the two feilds to floats so that my division gives a result as a float rather than 1 or 0.

Thanks again

Dav

4. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
...but you might want to rethink your statistics. AVG(A/B) can lead to skewed results if the values have a high variance. For instance, given these values for Creditors(A) and Assets(B)

A B
9 10
100 1000

..AVG(A/B) = 0.5! Record 1, with less than 1% of the assets has had 50% influence on the result. For most businesses purposes you would calculate your average as SUM(BSCLTradeCreditors)/SUM(BS_TOTAL_ASSETS). In the odd case where SUM(BS_TOTAL_ASSETS) is zero, (a very bad year?), you would use:

SELECT CCode,
CASE WHEN SUM(BS_TOTAL_ASSETS) = 0 THEN 0.0 ELSE SUM(BSCLTradeCreditors)/SUM(BS_TOTAL_ASSETS) END CASE) AS myaverage
FROM [Company/Year]
GROUP BY CCode

-or-

SELECT CCode,
SUM(BSCLTradeCreditors)/NULLIF(SUM(BS_TOTAL_ASSETS), 0) as myaverage
FROM [Company/Year]
GROUP BY CCode

...which (more accurately) yeilds NULL when then mathematical result is undefined.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•