how to divide SUM(CASE...)es?

1
votes

I need to gain percentage by dividing two SUM variables. But when I try the code below, it gives me 0 as result. How can I divide these two SUMs?

Thanks!

(SUM(Case When FinalApprovalDt is not null and ContractPayoutAmt > 0 Then 1 else 0 End) / SUM(Case when FinalApprovalDt is not null then 1 else 0 end)* 100) As WriteRate

3
sqlsql-server

3 Answers

3
votes

SQL Server does integer arithmetic, so 1/2 is 0 not 0.5.

You can write this by using a non-integer in the case expressions:

(SUM(Case when FinalApprovalDt is not null and ContractPayoutAmt > 0 Then 100.0 else 0 End) /
 SUM(Case when FinalApprovalDt is not null then 1 end)
) As WriteRate

Notice that I removed the else from the denominator. This helps prevent divide-by-zero errors.

You can also write this without an explicit division as:

AVG(Case when FinalApprovalDt is not null and ContractPayoutAmt > 0 Then 100.0 
         when FinalApprovalDt is not null then 0
    End) as WriteRate

This does an average over all rows where the FinalApprovalDt is not null, using the logic for the other column.

2
votes

Force one of the CASE expressions to have decimal precision:

1.0 * SUM(CASE WHEN FinalApprovalDt IS NOT NULL AND ContractPayoutAmt > 0
               THEN 1 ELSE 0 END) /
      SUM(CASE WHEN FinalApprovalDt IS NOT NULL
               THEN 1 ELSE 0 END)* 100 AS WriteRate

I suspect that SQL Server treated both CASE expressions as integers. When dividing integers, you would likely get truncation occurring. So for a quotient less than 1, you would just see zero.

Note: I don't know why you are dividing by 100. For a percentage, I would have expected you to multiply the numerator by 100.

0
votes

Please try like this ( Multiple the SUM of the numerator with 1.0 to make it decimal otherwise you will get integer division. )

SUM(Case When FinalApprovalDt is not null and ContractPayoutAmt > 0 Then 1 else 0 End) * 1.0
     / SUM(Case when FinalApprovalDt is not null then 1 else 0 end) * 100.0 ) As WriteRate