Sum columns with null values in oracle

Question

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             lt null gt   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         lt null gt    The wanted results  type    craft   total hours   A       1          9   B       1          8   B       2          9

User · Answer

You need to use the NVL function  e g   SUM NVL regular 0    NVL overtime 0

User · Answer

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

User · Answer

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

User · Answer

Code   select type  craft  sum coalesce  regular   overtime  regular  overtime   as total hours from hours t group by type  craft order by type  craft

User · Answer

select type  craft  sum NVL regular  0    NVL overtime  0   as total hours from hours t group by type  craft order by type  craft

User · Answer

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    one or more rows exist in the table  In this case I want the sum  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

User · Answer

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

User · Answer

select type  craft  sum nvl regular 0    nvl overtime 0   as total hours from hours t group by type  craft order by type  craft

User · Answer

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

[sql] Sum columns with null values in oracle

Examples related to sql

Examples related to oracle