CODEWITHSUNDEEP
sqloracle-database
Berek Bryan
Berek Bryan

Reputation: 14725

Sum columns with null values in oracle

I want to add two numbers together but when one of those numbers is null then the result is null. Is there a way around this. I could simply do it in the code but I would rather have it done in the query. This is a oracle database.

The table structure

hours_t
type     craft    regular       overtime
 A         1        5              0
 A         1        3              1
 B         2        9            <null>
 B         1        4              4

The query

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

The unwanted results

type   craft   total_hours
  A      1          9
  B      1          8
  B      2        <null>

The wanted results

type    craft   total_hours
  A       1          9
  B       1          8
  B       2          9

Upvotes: 32

Views: 155212

Answers (9)

MR AND
MR AND

Reputation: 406

Without group by SUM(NVL(regular, 0) + NVL(overtime, 0)) will thrown an error and to avoid this we can simply use NVL(regular, 0) + NVL(overtime, 0)

Upvotes: 0

Nikhil
Nikhil

Reputation: 163

In some cases, nvl(sum(column_name),0) is also required. You may want to consider your scenarios.

For example, I am trying to fetch the sum of a particular column, from a particular table based on certain conditions. Based on the conditions,

  1. one or more rows exist in the table. In this case I want the sum.
  2. rows do not exist. In this case I want 0.

If you use sum(nvl(column_name,0)) here, it would give you null. What you might want is nvl(sum(column_name),0).

This may be required especially when you are passing this result to, say, java, have the datatype as number there because then this will not require special null handling.

Upvotes: 2

Kumar SR
Kumar SR

Reputation: 1

Code:

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

Upvotes: 0

zendar
zendar

Reputation: 13572

NVL(value, default) is the function you are looking for.

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

Oracle have 5 NULL-related functions:

  1. NVL
  2. NVL2
  3. COALESCE
  4. NULLIF
  5. LNNVL

NVL: 

NVL(expr1, expr2)

NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.

NVL2 : 

NVL2(expr1, expr2, expr3)

NVL2 lets you determine the value returned by a query based on whether a specified expression is null or not null. If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3.

COALESCE

COALESCE(expr1, expr2, ...)

COALESCE returns the first non-null expr in the expression list. At least one expr must not be the literal NULL. If all occurrences of expr evaluate to null, then the function returns null.

NULLIF

NULLIF(expr1, expr2)

NULLIF compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1. You cannot specify the literal NULL for expr1.

LNNVL

LNNVL(condition)

LNNVL provides a concise way to evaluate a condition when one or both operands of the condition may be null.

More info on Oracle SQL Functions

Upvotes: 58

JosephStyons
JosephStyons

Reputation: 58685

The top-rated answer with NVL is totally valid. If you have any interest in making your SQL code more portable, you might want to use CASE, which is supported with the same syntax in both Oracle and SQL Server:

select 
  type,craft,
  SUM(
    case when regular is null
         then 0
         else regular
    end
    +
    case when overtime is null
         then 0
         else overtime
    end
  ) as total_hours
from 
  hours_t
group by
  type
 ,craft
order by
  type
 ,craft

Upvotes: 5

cletus
cletus

Reputation: 625077

The other answers regarding the use of nvl() are correct however none seem to address a more salient point:

Should you even have NULLs in this column?

Do they have a meaning other than 0?

This seems like a case where you should have a NOT NULL DEFAULT 0 on th ecolumn

Upvotes: 10

Turnkey
Turnkey

Reputation: 9406

You need to use the NVL function, e.g.

SUM(NVL(regular,0) + NVL(overtime,0))

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425371

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

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132580

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

Upvotes: 54

Related Questions