CODEWITHSUNDEEP
sqlsql-servercaseaverage
SkottyP
SkottyP

Reputation: 9

SQL Case average formula output issue

Everyone - I could use your help.

I have a SQL where I am checking a column to see if there is an amount. If there isn't I want it to output NULL. If it does I want it to calculate an average. I'm using this case statement and the NULL statement works fine, but my average statement is only spitting out 1 or 0. I've been searching and testing for a few hours with no luck.

CASE 
WHEN SUM(z.[SumTarget$]) = 0 THEN NULL
ELSE (1-(SUM(z.[SumBackorder])+SUM(z.[SumStockedOut]))/SUM(z.[CountofNo]))
END [SumAvPerc]

I have checked each field in the string by commenting out line 3 and using this instead to make sure the numbers are right coming into the formula, and so far everything checks out.

--ELSE SUM(z.[SumStockedOut])

Here is a sample of the fields and the output. **I couldn't post the picture because I need more reputation points :( **

Upvotes: 0

Views: 66

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

SQL Server only does integer division on intgers, so you should convert your values to non-integer numbers.

I often do this using * 1.0, something like:

(CASE WHEN SUM(z.[SumTarget$]) <> 0 
      THEN (1-(SUM(z.[SumBackorder])+SUM(z.[SumStockedOut]))/SUM(1.0 * z.[CountofNo]))
 END) as [SumAvPerc]

Upvotes: 2

Related Questions