SQL Server: Having trouble getting correct decimal value after cast

Question

Hello,

I'm trying to write a query that gives me a percentage (i.e. something like .885, for example) by dividing 2 aggregate numbers I selected via SUM. But my results are coming out as 0.0 instead of the correct number. So just for reference, starting with 2 queries, I have:

SELECT SUM(CASE WHEN Status_ID = 1 AND State_ID = 14 THEN 1 ELSE 0 END)
FROM SomeTable

Which yields 158. And:

SELECT SUM(CASE WHEN State_ID = 14 THEN 1 ELSE 0 END)
FROM SomeTable

Yields 203.

Now, if I were to do just this:

SELECT SUM(CASE WHEN Status_ID = 1 AND State_ID = 14 THEN 1 ELSE 0 END)/SUM(CASE WHEN State_ID = 14 THEN 1 ELSE 0 END)
FROM SomeTable

I would get 0, because everything is being used as integers. So I tried this:

SELECT CAST(SUM(CASE WHEN Status_ID = 1 AND State_ID = 14 THEN 1 ELSE 0 END)/SUM(CASE WHEN State_ID = 14 THEN 1 ELSE 0 END) AS DECIMAL(3,1))
FROM SomeTable

But my result is 0.0. And this is obviously not what I'd like. I'd like to be getting .778

I'm thinking that I need to be casting the numbers individually, but I tried that and got an arithmetic overflow exception. Does anyone see what I could be doing differently?

I'm using SQL Server 2005. Thanks very much.

Answer 1

what happens when you do

THEN 1.0 ELSE 0.0

you can also cast each number that you are dividing, there is no point casting the sum

see also

select 3/2 --1
select 3/2.0 --1.500000
select 3/convert(decimal(3,2),2) --1.500000

So this is one way

SELECT SUM(convert(decimal(3,2),
CASE WHEN Status_ID = 1 AND State_ID = 14 THEN 1 ELSE 0 END))/
SUM(convert(decimal(3,2),CASE WHEN State_ID = 14 THEN 1 ELSE 0 END))

To handle division by 0 do this

SELECT CASE SUM(convert(decimal(3,2),CASE WHEN State_ID = 14 THEN 1 ELSE 0 END))
when 0 then 0 else SUM(convert(decimal(3,2),
CASE WHEN Status_ID = 1 AND State_ID = 14 THEN 1 ELSE 0 END))/
SUM(convert(decimal(3,2),CASE WHEN State_ID = 14 THEN 1 ELSE 0 END)) END

See also here: SQL Server efficient handling of divide by zero

Answer 2

You had the right idea, you just didn't go far enough with it.

SELECT CAST(SUM(CASE WHEN Status_ID = 1 AND State_ID = 14 THEN 1 ELSE 0 END) AS DECIMAL(3,1))/CAST(SUM(CASE WHEN State_ID = 14 THEN 1 ELSE 0 END) AS DECIMAL(3,1)) 
FROM SomeTable 

Answer 3

Your expression is doing the integer division and then converting to decimal. You have to change the expression so that it does the division as decimal. Setting the constants as decimals (1.0, 0.0) or casting at least one integer as decimal should avoid the problem.