Fixing divide by zero error with case statement

2
votes

I have inherited some code with a divide by zero error.

I keep getting the error and not sure if it is the code or where I am placing it. Should this line be added or replace a line?

Tried adding and replacing with this case statement: CASE WHEN SUM(PREOP_MME) = 0 THEN 0 ELSE SUM(PREOP_MME) / SUM(COUNT_CASES) END AS PREOP_MME).

SELECT 

PROCEDURE_NM
,SUM(PreOp_MME)     / SUM(COUNT_CASES) PreOp_MME
,SUM(IntraOp_MME)   / SUM(COUNT_CASES) IntraOp_MME 
,SUM(PostOp_MME / PostOp_LOS) PostOp_MME
,SUM(Discharge_MME) / SUM(COUNT_CASES) Discharge_MME
,SUM(TOTAL_OVERALL_MME) / SUM(COUNT_CASES) Overall_MME


FROM (

SELECT DISTINCT 

Spine.LOG_ID
,Spine.SERV_AREA_ID [Service Area ID]
,Spine.LOC_ID [Revenue Location ID] 
,Spine.PRIMARY_PHYS_ID [Provider ID]
,Spine.SURGERY_DATE
,Spine.COUNT_CASES
,Spine.PROCEDURE_NM
,Spine.PostOp_LOS

,(PreOp.SUM_SIG * PreOp.PreOp_MME)          AS PreOp_MME
,(IntraOp.SUM_SIG * IntraOp.IntraOp_MME)    AS IntraOp_MME
,(PostOp.SUM_SIG * PostOp.PACU_MME)         AS PostOp_MME
,DischMeds.DOSE_MME                 AS     Discharge_MME

,(PreOp.SUM_SIG * PreOp.PreOp_MME) + 
 (IntraOp.SUM_SIG * IntraOp.IntraOp_MME) + 
 (PostOp.SUM_SIG * PostOp.PACU_MME) + DischMeds.DOSE_MME as TOTAL_OVERALL
3
sql-server
I gues you invert the values. because if A = 0 then A / anything is also 0 and you wont need a case - Juan Carlos Oropeza
Btw what error are you getting? because in mysql A / 0 doesnt give error, just return null. - Juan Carlos Oropeza
Are you use the DB is MySql ? alias like [Service Area ID] say MSSQL. MySql should looks like `Service Area ID` - Juan Carlos Oropeza
That was a mistake - This is MSSql. - user11494577

3 Answers

2
votes

You should check for the divisor ( denominator) and not for the divided (numerator) is not equal to 0 so for the first column you coudl try

SELECT 

    PROCEDURE_NM, 
    case when SUM(COUNT_CASES) = 0 
                  then 0 
                  else SUM(PreOp_MME)/ SUM(COUNT_CASES) end PreOp_MME,
    .....
1
votes

Just use COALESCE

Here I build a small demo as example

SELECT id,
       SUM(`value1`) / SUM(`value2`) as _before,
       COALESCE (SUM(`value1`) / SUM(`value2`) , 0) as _after
FROM Table1
GROUP by id;
0
votes

Thank you for your answers.

I used the CASE WHEN SUM(FIELD) = 0 THEN 0 ELSE SUM(FIELD) / SUM(FIELD) END AS

My error was because I was not accounting for zeros in each instance, only the first. Must be time to go home!