Sum Columns with Null Values in Oracle

Sum columns with null values in oracle

select type, craft, sum(nvl(regular,0) + nvl(overtime,0)) as total_hours
from hours_t
group by type, craft
order by type, craft

SUM function consider NULL on Oracle 12c

Aggregate functions (SUM, AVERAGE etc) generally ignore NULL values. Therefore if there is at least one non null value in the column, you will get a non null result.

If all values in a column being aggregated are null, there is pretty much no other option but to return null as the result.

Note that this is different to how scalar functions behave. For example: "Select x + y" will return null if either x, y or both are null. You will only get a non null result for this if both x and y have non-null values.

SQL: sum 3 columns when one column has a null value without replacing null with 0?

This solution worked for me

select 
case when coalesce(sum(ONE), sum(TWO), sum(THREE)) is null then null else 
sum(nvl(ONE,0) + nvl(TWO,0) + nvl(THREE,0)) end as 
TOTALSUM
GROUP BY ID, ANOTHERID; 

Why SUM(null) is not 0 in Oracle?

SQL does not treat NULL values as zeros when calculating SUM, it ignores them:

Returns the sum of all the values, or only the DISTINCT values, in the expression. Null values are ignored.

This makes a difference only in one case - when the sequence being totalled up does not contain numeric items, only NULLs: if at least one number is present, the result is going to be numeric.

ORACLE, ignore null to sum

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

SUM in Oracle: return NULL only when all fields are NULL

I need both of these SUMS to evaluate to NULL only if all of the
values summed are null.

It will by default. Just replace 0 with null in your ELSE condition.

    09:43:30 SYSTEM@dwal> ed
Wrote file S:\spool\dwal\BUFFER_SYSTEM_65.sql

  1  with t (x, y) as (
  2    select null, 1  from dual union all
  3    select null, 1  from dual union all
  4    select null, 2 from dual
  5  )
  6* select sum(x), sum(case y when 1 then x else null end ) from t
09:43:40 SYSTEM@dwal> /

    SUM(X) SUM(CASEYWHEN1THENXELSENULLEND)
---------- -------------------------------

Elapsed: 00:00:00.00
09:43:41 SYSTEM@dwal> 2
  2*   select null, 1  from dual union all
09:43:59 SYSTEM@dwal> c/null/1
  2*   select 1, 1  from dual union all
09:44:03 SYSTEM@dwal> /

    SUM(X) SUM(CASEYWHEN1THENXELSENULLEND)
---------- -------------------------------
         1                               1

Elapsed: 00:00:00.01

SQL: sum 3 columns when one column has a null value?

If the column has a 0 value, you are fine, my guess is that you have a problem with a Null value, in that case you would need to use IsNull(Column, 0) to ensure it is always 0 at minimum.

---

Related Topics