CODEWITHSUNDEEP
sqlsql-serverstored-procedures
SBB
SBB

Reputation: 8970

SQL divide by zero error - ISNULL not working?

I have a query I am working on that does some math, specifically totals and percentages. Due to this data being optional based on the date range selected, there are times where theres nothing that it can run this math against.

I am getting a divide by zero error in this instance.

    SELECT (
        SELECT COUNT(s.ScoreID) AS TotalSamples,
                 COUNT(CASE WHEN qd.QDResultDesc = 'Pass' THEN 1 END) AS Pass,
                 COUNT(CASE WHEN qd.QDResultDesc = 'Pass' THEN 1 END) * 100 / COUNT(s.ScoreID) AS Quality,
                 COUNT(CASE WHEN qd.QDResultDesc = 'Fail' THEN 1 END) AS Fail,
                 COUNT(CASE WHEN qd.QDResultDesc = 'Fail' THEN 1 END) * 100 / COUNT(s.ScoreID) AS Error
            FROM SubmissionScore AS s
            JOIN gSubmission AS sub
            ON sub.SubmissionID = s.RecordID
            JOIN gQDResult AS qd
            ON s.QDResultID = qd.QDResultID
            JOIN resources.emp.Employees as e
            ON sub.TeammateQID = e.QID
            WHERE e.CampusID = 66 -- Chennai
        FOR    XML PATH ('overall'), ELEMENTS, TYPE, ROOT ('chennai')
    )
FOR    XML PATH ('data'), ELEMENTS, TYPE, ROOT ('root');

I have tried adding ISNULL to account for missing pieces but that doesn't seem to fix it.

In the query above, if there is no data, I would expect the output to just be 0.

Is there an easy way to tweak this code to account for this?

Upvotes: 2

Views: 118

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

You are looking for NULLIF(), not ISNULL():

            COUNT(CASE WHEN qd.QDResultDesc = 'Pass' THEN 1 END) * 100 / NULLIF(COUNT(s.ScoreID), 0) AS Quality,

You may also want * 100.0, because SQL Server does integer arithmetic.

Upvotes: 8

Related Questions