Convert null values to zero in crosstab query

Question

The code below is a subset of a larger code that results in a cross tab query. The issue is that the total column to the right which sums all the number columns in the cross tab query does not calculate accurately as it results a 0 for any rows that have null value in one of the twelve columns it is attempting to sum.

I believe that the i need to add a condition to the following line in the code to result in a zero if the value is null. I just need someone to take a second look at it. If there is a better alternate solution, I am happy to entertain that also.

SUM(entry) for MX in (M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12)

        SELECT 
         tblCenters.prop
        ,tblAccounts.Category_EXCO
        ,tblCenters.CenterNum
        ,tblAccounts.AccountNum
        ,tblAccounts.Account_Description
        ,tblAccounts.Is_Revenue
        ,tblAccounts.Is_Above_EBITDA
        ,tblCenters.Division_Description
        ,tblCenters.[Is_F&B1]
        ,tblCenters.Group_Description
        ,Entry
        ,MX
            FROM GA_Financial.dbo.tblSAP 

            left join tblMX on tblSAP.MDY = tblMX.MDY
            left join tblAccounts on tblSAP.AccountNum = tblAccounts.AccountNum
            left join tblCenters on tblSAP.CenterNum = tblCenters.CenterNum and tblSAP.Prop_SAP = tblCenters.PROP_SAP

            WHERE tblAccounts.Is_Above_EBITDA = 1
            AND tblSAP.Type = 'A'

        ) 
    AS Tab1
--The code below breaks down column "Entry" into twelve individual monthly columns and fills columns M1 through M12
    PIVOT
        (

        SUM(entry) for MX in (M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12)
        ) as TAb2

        where Prop = 'RWNY'

score 0 · Answer 1 · answered Apr 09 '19 at 20:37

0

I think that you can use the COALESCE or IFNULL functions to replace the null value with a 0 for the sum calculation. Check out this response: COALESCE, IFNULL, or NZ() function that can be used in SQL Server and MS Access

answered Apr 09 '19 at 20:37

Hopper

146
6

score -1 · Answer 2 · edited Apr 09 '19 at 23:59

-1

To avoid a null result use ISNULL(Entry, 0) instead of Entry in your original query. This will substitute any returned null's with 0's.

edited Apr 09 '19 at 23:59

Arnaud Peralta

1,287
1
16
20

answered Apr 09 '19 at 20:37

Maclean

1
1

Hello Maclean. Can you please be specific as to which part of the code i should made this edit to? The reference to Entry is made in multiple sections of the query. Do you think the edit should be made to the reference in the PIVOT section? – Bill Singh Apr 09 '19 at 21:10
Make the change in the initial selection statement. SELECT tblCenters.prop ,tblAccounts.Category_EXCO ,tblCenters.CenterNum ,tblAccounts.AccountNum ,tblAccounts.Account_Description ,tblAccounts.Is_Revenue ,tblAccounts.Is_Above_EBITDA ,tblCenters.Division_Description ,tblCenters.[Is_F&B1] ,tblCenters.Group_Description ,isnull(Entry,0) -- replaced Entry to handle potential null values ,MX FROM GA_Financial.dbo.tblSAP – Maclean Apr 10 '19 at 13:33

Convert null values to zero in crosstab query

2 Answers2