How to sum values from COALESCE with NULL values

Question

I was wondering if it is possible to sum values used in coalesce even though some of them are null. Currently, if any value from coalesce returns null, the result of the sum is null as well.

Here is a SQL fiddle with the basic case: http://sqlfiddle.com/#!17/9eecb/83134

CREATE TABLE IF NOT EXISTS table1 (
   item_a INT,
   item_b INT,
   item_c INT
);

CREATE TABLE IF NOT EXISTS table2 (
   item_a INT,
   item_b INT,
   item_c INT
);

INSERT INTO table1(item_a, item_b, item_c)
VALUES (3, NULL, NULL);
INSERT INTO table2(item_a, item_b, item_c)
VALUES (NULL, 2, NULL);

SELECT (NULLIF(COALESCE (t1.item_a, t2.item_a),0) +
       NULLIF(COALESCE (t1.item_b, t2.item_b),0) +
       NULLIF(COALESCE (t1.item_c, t2.item_c),0)) as item_sum,
       COALESCE (t1.item_a, t2.item_a) as item_a,
       COALESCE (t1.item_b, t2.item_b) as item_b,
       COALESCE (t1.item_c, t2.item_c) as item_c
FROM table1 t1, table2 t2

The previous query should return 5 as item_sum (3 + 2). However, the last column item_c in both tables is null, so the result is NULL instead of 5.

Thanks

Why are you effectively cancel the effect of `coalesce()` with `nullif()` (plus some other side effects)? You may rather want another nested `coalesce()` that yields `0`, if both columns are `NULL`? — sticky bit, Oct 26 '21 at 01:20
Side note: 9.4 is out of support for a long time. Consider upgrading soon. — sticky bit, Oct 26 '21 at 01:21

score 1 · Answer 1 · answered Oct 26 '21 at 01:24

COALESCE

The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display

SELECT COALESCE (t1.item_a, t2.item_a, 0) +
       COALESCE (t1.item_b, t2.item_b, 0) +
       COALESCE (t1.item_c, t2.item_c, 0) as item_sumA,
       COALESCE (t1.item_a, t2.item_a) as item_a,
       COALESCE (t1.item_b, t2.item_b) as item_b,
       COALESCE (t1.item_c, t2.item_c) as item_c
FROM table1 t1, table2 t2

fiddle

How to sum values from COALESCE with NULL values

1 Answers1