CODEWITHSUNDEEP
oracle-databasesum
Yaser
Yaser

Reputation: 57

ORACLE, ignore null to sum

I want to add two columns(number type) of a row, together but when one of those columns is null then the result is null. I handlled it by NVL function(NVL(col1,0) + NVL(col2,0)). But i want to return null when both are null(instead of 0). how can i handle it? Is there function in ORACLE that ignore null to sum two columns of a row?

| col1 | col2  |                                     |
|:---- |:-----:|:-----------------------------------:|
|  1   |   2   |      --> result have to be : 3      |
|  1   |  null |     --> result have to be : 1       |
| null |  null |     --> result have to be : null    |

Upvotes: 1

Views: 206

Answers (2)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

coalesce(col1+col2,col1,col2) would be easier:

with t(col1,col2) as (
  select 0,1 from dual union all
  select 2,null from dual union all
  select null,3 from dual union all
  select null,null from dual
  )
select 
  col1,col2,
  coalesce(col1+col2,col1,col2) sum_cols  
from t;

Results:

      COL1       COL2   SUM_COLS
---------- ---------- ----------
         0          1          1
         2       null          2
      null          3          3
      null       null       null

4 rows selected.

or subquery with sum if you have more columns to sum:

(select sum(column_value) from table(sys.odcinumberlist(col1,col2,...,colN)))

Example:

with t(col1,col2) as (
  select 0,1 from dual union all
  select 2,null from dual union all
  select null,3 from dual union all
  select null,null from dual
  )
select 
  col1,col2,
  coalesce(col1+col2,col1,col2) sum_cols,
  (select sum(column_value) from table(sys.odcinumberlist(col1,col2))) sum_cols2
from t;

DBFiddle: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=eda8de9746f1d0def3c290420adbb705

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

A CASE expression might be the easiest way to go here:

SELECT CASE WHEN col1 IS NULL AND col2 IS NULL
            THEN NULL
            ELSE NVL(col1, 0) + NVL(col2, 0) END AS output
FROM yourTable;

Upvotes: 1

Related Questions