在Oracle中对具有空值的列求和 [英] Sum columns with null values in oracle

问题描述

我想将两个数字加在一起，但是当这些数字之一为null时，结果为null.有没有解决的办法.我可以在代码中简单地做到这一点，但我宁愿在查询中完成它.这是一个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.

表结构

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

查询

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

不良结果

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

所需结果

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

推荐答案

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中对具有空值的列求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！