Why Sum(Null) Is Not 0 in Oracle

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.

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.

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

Changing a SUM returned NULL to zero

Put it outside:

SELECT COALESCE(

(  
    SELECT SUM(i.Logged)  
    FROM tbl_Sites s  
    INNER JOIN tbl_Incidents i  
    ON s.Location = i.Location  
    WHERE s.Sites = @SiteName AND i.[month] = DATEADD(mm, DATEDIFF(mm, 0, GetDate()) -1,0)  
    GROUP BY s.Sites  
), 0)  AS LoggedIncidents

If you are returning multiple rows, change INNER JOIN to LEFT JOIN

SELECT COALESCE(SUM(i.Logged),0)
FROM tbl_Sites s  
LEFT JOIN tbl_Incidents i  
ON s.Location = i.Location  
WHERE s.Sites = @SiteName AND i.[month] = DATEADD(mm, DATEDIFF(mm, 0, GetDate()) -1,0)  
GROUP BY s.Sites  

By the way, don't put any function or expression inside aggregate functions if it's not warranted, e.g. don't put ISNULL, COALESCE inside of SUM, using function/expression inside aggregation cripples performance, the query will be executed with table scan

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 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 in database query giving NULL

Just use coalesce [ with 0 as the second argument ] to replace nulls for all month columns, otherwise you can not get true results from aggregation of numeric values :

select sum(coalesce(January,0)+coalesce(February,0) ... )
  from Expense

why addition need to check null but sum not need to check null?

I will post a speculation that in the case of aggregate functions having the default behavior be to ignore NULL values agrees with the desired behavior the majority of the time.

Consider the average function AVG in Oracle, MySQL or most other databases. Imagine that we have a column of values which also include a few nulls. By having AVG ignore nulls we get the same result as if we had replaced those nulls with the average of all the non NULL values. This would be a common technique which a statistician might employ to report an average when a few data points were unknown. So from a statistics point of view, ignoring nulls makes sense, at least in this one very common scenario.

On the other hand, in the case of basic arithmetic, making such a presumption about nulls would not go over so well. Consider a + b + c where any column might be NULL. Making an assumption about what NULL represents could be dangerous and misleading, and could result in a DBA concluding that the value of the expression is known when in fact it is not. In this case, we could still ignore nulls or replace them, and the COALESCE function (or something similar) exists, but it must be used manually.

My Select SUM query returns null. It should return 0

Try this:

select COALESCE(sum(balance),0) from mytable where customer = 'john' 

This should do the work. The coalesce method should return the 0.

---

Related Topics