How to convert null rows to 0 and sum the entire column using DB2?

Question

I'm using the following query to sum the entire column. In the TOREMOVEALLPRIV column, I have both integer and null values.

I want to sum both null and integer values and print the total sum value.

Here is my query which print the sum values as null:

select 
    sum(URT.PRODSYS) as URT_SUM_PRODSYS,
    sum(URT.Users) as URT_SUM_USERS,
    sum(URT.total_orphaned) as URT_SUM_TOTAL_ORPHANED,
    sum(URT.Bp_errors) as URT_SUM_BP_ERRORS,
    sum(URT.Ma_errors) as URT_SUM_MA_ERRORS,
    sum(URT.Pp_errors) as URT_SUM_PP_ERRORS,
    sum(URT.REQUIREURTCBN) as URT_SUM_CBNREQ,
    sum(URT.REQUIREURTQEV) as URT_SUM_QEVREQ,
    sum(URT.REQUIREURTPRIV) as URT_SUM_PRIVREQ,
    sum(URT.cbnperf) as URT_SUM_CBNPERF,
    sum(URT.qevperf) as URT_SUM_QEVPERF,
    sum(URT.privperf) as URT_SUM_PRIVPERF,
    sum(URT.TO_REMOVEALLPRIV) as TO_REMOVEALLPRIV_SUM
  from
    URTCUSTSTATUS URT
    inner join CUSTOMER C on URT.customer_id=C.customer_id;

Output image:

Expected Output:

Instead of null, I need to print sum of rows whichever have integers.

Answer 1

The SUM function automatically handles that for you. You said the column had a mix of NULL and numbers; the SUM automatically ignores the NULL values and correctly returns the sum of the numbers. You can read it on IBM Knowledge Center:

The function is applied to the set of values derived from the argument values by the elimination of null values.

Note: All aggregate functions ignore NULL values except the COUNT function. Example: if you have two records with values 5 and NULL, the SUM and AVG functions will both return 5, but the COUNT function will return 2.

However, it seems that you misunderstood why you're getting NULL as a result. It's not because the column contains null values, it's because there are no records selected. That's the only case when the SUM function returns NULL. If you want to return zero in this case, you can use the COALESCE or IFNULL function. Both are the same for this scenario:

COALESCE(sum(URT.TO_REMOVEALLPRIV), 0) as TO_REMOVEALLPRIV_SUM

or

IFNULL(sum(URT.TO_REMOVEALLPRIV), 0) as TO_REMOVEALLPRIV_SUM

I'm guessing that you want to do the same to all other columns in your query, so I'm not sure why you only complained about the TO_REMOVEALLPRIV column.