DB2 SELECT which sums two columns and handles null values

coalesce
sql sum(case when null then 0)
handle null values in sum sql
adding columns with null values
sql sum null as 0
sql sum multiple columns
sql server sum(null as 0)
addition with null in sql

Let's say I have flowing table MY_TABLE with columns A and B:

NULL, NULL
NULL, 1
1, NULL,
1, 1
0, 0

What I need is a select which returns:

NULL
1
1
2
0
SELECT A + B
  FROM MY_TABLE

will result in:

NULL
NULL
NULL
2
0

Currently, I have written new MY_SUM function so I can have:

SELECT MY_SUM(A, B)
FROM MY_TABLE
CREATE FUNCTION MY_SUM(IN decimalNumber1 DECIMAL(20, 2), IN decimalNumber2 DECIMAL(20, 2))
RETURNS DECIMAL(20, 2)
BEGIN

    DECLARE decimalSum DECIMAL(20, 2);

    IF decimalNumber1 IS NOT NULL THEN
        SET decimalSum = decimalNumber1;
    END IF;

    IF decimalNumber2 IS NOT NULL THEN 
        IF decimalSum IS NULL THEN
            SET decimalSum = decimalNumber2;
        ELSE
            SET decimalSum = decimalSum + decimalNumber2;
        END IF;
    END IF;

    RETURN decimalSum;
END

but I was wondering if there is a better out-of-the-box way for doing this?

Try this:

select case when a is null and b is null then null else coalesce(a,0)+coalesce(b,0) end from my_table

or

"select case when coalesce(a,b) is null then null else coalesce(a,0)+coalesce(b,0) end from my_table

There are many other ways to do it.

Nulls: Nulls and Aggregate Functions, What will be the output of given statement select sum null? Allowing null values for the SALARY column; Using a non-null default value (such as, 0) By allowing null values, you can formulate the query easily, and DB2® provides the average of all known or recorded salaries. The calculation does not include the rows that contain null values.

The following expression works in your case:

COALESCE(A, B) - COALESCE(A, B) + COALESCE(A, 0) + COALESCE(B, 0)

PostgreSQL SUM Function, , 5] in 4 columns, it will give the output as 8. 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.

COALESCE will do the job - your problem is not fully described but check out this

WITH temp  (a, b) AS (
VALUES (NULL, NULL), (NULL, 1), (1, NULL), (1, 1)
)

SELECT COALESCE(a,0) + COALESCE(b,0)
FROM temp
WHERE COALESCE(a,0) + COALESCE(b,0) <> 0

SQL Tip: COUNTing NULL values – Benjamin's Blog, MySQL and PostgreSQL cannot sum up NULL values with the + value. The sum value will be NULL . MySQL: test_database=# select 1 + 2 + 3; ?column? I use WinSQL to execute a query. It takes the sum of a column. Within the column, there are several NULL values and winSQL complains that it cannot add NULL values. Does anybody have an idea how to get it work? Thanks SELECT SUM(column) SUM(), as documented in the SQL Reference, ignores NULL values in the column.

There is not a built-in way to transparently interpret NULLs as 0s.

Probably the closest you can get is with a function like this

CREATE OR REPLACE FUNCTION MY_SUM(
    n1 DECFLOAT DEFAULT NAN
   ,n2 DECFLOAT DEFAULT NAN
   ,n3 DECFLOAT DEFAULT NAN
   ,n4 DECFLOAT DEFAULT NAN
   ,n5 DECFLOAT DEFAULT NAN
) RETURNS DECFLOAT
RETURN
    CASE WHEN COALESCE(NULLIF(n1,NAN),NULLIF(n2,NAN)) IS NOT NULL THEN
  + COALESCE(NULLIF(n1,NAN),0)
  + COALESCE(NULLIF(n2,NAN),0)
  + COALESCE(NULLIF(n3,NAN),0)
  + COALESCE(NULLIF(n4,NAN),0)
  + COALESCE(NULLIF(n5,NAN),0)
  END

which you can extend to support more columns as needed

SELECT MY_SUM(a,b) AS SUM
FROM TABLE(VALUES (NULL, NULL), (NULL, 1), (1, NULL), (1, 1)) T(a,b)

would then return

 S
 -

 1
 1
 2

Note The function could be simpler if you create one function for each number of parameters you want to sum, but the single function with DEFAULTs is neater

Is it safe to use SUM() without ISNULL(), For comparison in a SELECT statement, two null columns are not considered equal. When a nullable column participates in a predicate in the WHERE or HAVING clause, the These are all meaningless because null is the absence of a value. SQL queries and the effect nulls have on them: SELECT JOB, SUM(​SALARY)  If the expression includes more than one column name, each column name must be a correlated reference to the same group. The result of the COUNT and COUNT_BIG functions cannot be the null value. As specified in the description of AVG, MAX, MIN, STDDEV, SUM, and VARIANCE, the result is the null value when the function is applied to an empty set.

Don't sum up columns with + in a SQL query if NULL-values can be , 2. Evaluate each SQL statement in the DB2 Explain: • Check for any Check for any nonmatching Index Scans (Index usage with 0 matching columns). AVG, MIN, MAX, or SUM statement, make sure the SQL is coded to handle a null value. SQL AVG() function: SQL AVG function calculates the average value of a column of numeric type. The SQL AVG() function calculates NON NULL values. The SQL WHERE clause is used along with SQL AVG() function to get the result in a specific format based on one or more conditions.

DB2 Developer's Guide: A Solutions-Oriented Approach to Learning , Is the SQL coded to handle a null value if no actual value exists for some rows? Does each SQL statement need all the columns being selected? - For every AVG, MIN, MAX, or SUM statement, make sure the SQL is coded to handle a null value. Most all predicates containing 'Not' logic are non indexable and Stage 2. The function is applied to the set of values derived from the argument values by the elimination of null values. If the function is applied to an empty set, the result is the null value. Otherwise, the result is the maximum value in the set. The specification of DISTINCT has no effect on the result and is not advised.

DB2 SQL Tuning Tips for z/OS Developers: DB2 SQL Tunin Tips Devel, The SUM() function returns NULL if the set is empty or all values are NULL . Note that the First, create a new table named sum_demo that contains one integer column: CREATE values. SELECT SUM(DISTINCT c1) total_distinct FROM sum_demo; 2) Using DB2 SUM() function with the GROUP BY clause example. If you want to get value + null = value, then you should use List.Sum finction, like in that example: List.Sum({[A], [B], [C]}) THE SAME BEHAVIOR Power Query shows when you’ll try to multiply two columns and three or more columns: with two columns there will be the simple * symbol, with three or more columns there will be List.Product

Comments
  • To summarize your request you want NULL to be treated as 0 when there's one of them but stay NULL if there's two of them. They reason this doesn't exist out of the box is because that is not how NULL works.
  • Your comment really got me thinking. This really is an unusual situation because user must not input value when the value has not been provided. On the other hand, when time comes, we need to sum all available values. SUM function for an example does something similar. It too ignores null values when summing through rows. Only difference is that sum is an agregate function that goes through rows.
  • So, there is no out of the box solution? The best I can do is write code more concise by using coalesce?
  • Just to clarify second one, this part is taking care of "both null" cases: COALESCE(A, B) - COALESCE(A, B) and this provides the sum value: COALESCE(A, 0) + COALESCE(B, 0)
  • Correct. I’ve removed the 1-st expression to satisfy your updated example, where 0+0 must be equal to 0.
  • I'm sorry, I've updated the question with more examples to make it more clearer. Two NULLs must not be converted to a 0 or two zeroes to NULL. Thank you for the reply though.
  • use a CASE to get what you need