Sum columns with null values in oracle
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
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:
- NVL
- NVL2
- COALESCE
- NULLIF
- 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
select type, craft, sum(nvl(regular,0) + nvl(overtime,0)) as total_hours
from hours_t
group by type, craft
order by type, craft
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