时间戳

首页 > 解决方案 > 如何在 SUM CASE WHEN 语句中将 null 替换为 0？

sql - 如何在 SUM CASE WHEN 语句中将 null 替换为 0？

问题描述

我正在使用 CASE WHEN 语句和 SUM 函数。我想用 0 替换所有空值。

我尝试在我的声明末尾添加一个 0 。

这是我当前的代码

select    ID, PT_Number,
          ORGANIZATION,
          [Approved] = sum(case when PT_TYPE = 'Lend' then Approved END),
          [Disbursed] = sum(case when PT_TYPE = 'Give' then Disbursed end),
          [Repaid] = sum(case when PT_TYPE = 'Pay' then Payment end)
from      TABLE1
group by  ID, PT_Number,
          ORGANIZATION

这就是我正在尝试的

          [Approved] = sum(case when PT_TYPE = 'Lend' then Approved END, 0),
          [Disbursed] = sum(case when PT_TYPE = 'Give' then Disbursed end, 0),
          [Repaid] = sum(case when PT_TYPE = 'Pay' then Payment end, 0)

标签： sqltsql

解决方案

只需添加一个else子句：

select    ID, PT_Number,
          ORGANIZATION,
          [Approved] = sum(case when PT_TYPE = 'Lend' then Approved else 0 END),
          [Disbursed] = sum(case when PT_TYPE = 'Give' then Disbursed else 0 end),
          [Repaid] = sum(case when PT_TYPE = 'Pay' then Payment else 0 end)
from  TABLE1
group by ID, PT_Number, ORGANIZATION;

您也可以使用coalesce()，但这似乎有点矫枉过正。

推荐阅读