Handling NULL values in an SQL Query that uses SUM

Question

The Problem:

I'm trying to execute a query in VBA (Excel) containing a calculation using SUM e.g.

Select SUM(field1) - SUM(field2) as 'newField' from y.

If there are no null values or empty fields then the code works perfectly, however as soon as I have one null/empty value in either field1 or field2 then a blank is returned.

So if SUM(field1) = 17 and SUM(field2) = null I want to see the query return 17 instead of a blank value.

The Code:

Public Function GetRiskRecords(ByVal strPrgNo As String) As Variant

Dim strSQL As String

strSQL = "Select [tblRiskMitigating$].RiskID, [tblRiskMitigating$].Program_No, [tblRiskMain$].RiskID, [tblRiskMain$].Program_No, [tblRiskMain$].Department, [tblRiskMain$].DateAdded, [tblRiskMain$].OwnerID, [tblRiskMain$].Description, " _
        & " [tblRiskMain$].RiskTo, [tblRiskMain$].Probability, " _
        & " Sum([tblRiskMain$].ScheduleImpact) - Sum([tblRiskMitigating$].ScheduleReduction) as 'NewScheduleImpact', " _
        & " Sum([tblRiskMain$].CostImpact) - Sum([tblRiskMitigating$].CostReduction) as 'NewCostImpact'" _
        & " From [tblRiskMain$]" _
        & " Left Join [tblRiskMitigating$] on [tblRiskMain$].RiskID = [tblRiskMitigating$].RiskID AND [tblRiskMain$].Program_No = [tblRiskMitigating$].Program_No" _
        & " Where [tblRiskMain$].Program_No = '" & strPrgNo & "'" _
        & " Group By [tblRiskMitigating$].RiskID, [tblRiskMitigating$].Program_No, [tblRiskMain$].RiskID, [tblRiskMain$].Program_No, [tblRiskMain$].Department, [tblRiskMain$].DateAdded, [tblRiskMain$].OwnerID, [tblRiskMain$].Description, " _
        & " [tblRiskMain$].RiskTo, [tblRiskMain$].Probability"

Call Open_Conn(strDb)
Call Open_RS(strSQL)

If rs.EOF Then
    Debug.Print ("Error: no records")
Else
    GetRiskRecords = rs.GetRows
End If

Call Close_RS
Call Close_Conn

End Function

The Background:

I have done a bit of digging around and have experimented with adding isnull and iif at the start but I haven't been able to get these to work at all (so I've omitted them from the question to reduce confusion from my original (main) question).

@pnuts Just trying to be polite, will keep it in mind in the future. — ppw
– ppw, Commented Nov 11, 2015 at 15:45

Lukasz Szozda · Accepted Answer · 2015-11-11 15:36:03Z

4

You can use ISNULL/COALESCE:

Select SUM(ISNULL(field1,0)) - SUM(ISNULL(field2,0)) as 'newField' 
from y;

or even combine to one SUM:

Select SUM(ISNULL(field1,0) - ISNULL(field2,0)) as 'newField' 
from y;

If ISNULL/COALESCE can be easily interchanged with CASE WHEN or IIF(SQL Server 2012+):

CASE WHEN field1 IS NULL THEN 0
     ELSE field1
END;

IIF(field1 IS NULL, 0, field1);

EDIT:

Above code is SQL Server oriented, for EXCEL you should use:

SELECT SUM(IIF(field1 IS NULL, 0, field1) - IIF(field2 IS NULL, 0 field2)) 
FROM y

edited Nov 11, 2015 at 15:36

answered Nov 11, 2015 at 15:17

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ppw Over a year ago

I'm getting an error "Wrong number of arguments" when I try your suggestion. (I've changed nothing but added the syntax as recommended)

ppw Over a year ago

I have replaced line 3 of the above strSQL with Sum(ISNULL([tblRiskMain$].ScheduleImpact,0) - ISNULL([tblRiskMitigating$].ScheduleReduction,0)) as 'NewScheduleImpact'

Lukasz Szozda Over a year ago

@ppw Try with IIF version SUM(IIF(field1 IS NULL, 0, field1) - IIF(field2 IS NULL, 0 field2)) or SUM(IIF(ISNULL(field), 0, field1) - IIF(ISNULL(field2), 0 field2))

ppw Over a year ago

The IIF version with IS NULL syntax worked like a charm, thank you very much for your help! Answer accepted.

Collectives™ on Stack Overflow

Handling NULL values in an SQL Query that uses SUM

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related