sql-server - SUM 一列然后减去另一列的 SUM

Question

我有一个包含以下字段的表:

EmpID,
Code,
Amount,
TransDate,
CM,
CMDate

我想要进入数据网格的是 SUM所有的 Amount具有相同的 Code和 SUM CM具有相同的 Code如 Amount然后减去 SUM(Amount)到 SUM(CM)但如果结果是 NULL (例如，没有 CM 到 SUM 和减去 SUM(Amount) )然后它只需要选择 SUM(Amount) .我想出了这个声明:

SELECT
    TransDate, 
    Code, 
    SUM(Amount) - SUM(CM) AS 'Result'
FROM DeductionTbl
WHERE 
    (EmpID = @empID)
GROUP BY Code, TransDate
ORDER BY TransDate

如果有 SUM(CM)，我会得到结果值减去 SUM(Amount)但我得到了结果 NULL如果没有 SUM(CM) 的值相同的 Code .我不想要 NULL数据网格上的值。如果有 NULL值只是返回 SUM(Amount) .有没有办法做到这一点？谢谢!

Answer 1

使用 ISNULL s:

SELECT
    TransDate, 
    Code, 
    isnull(SUM(isnull(Amount,0)),0) - isnull(SUM(isnull(CM,0)),0) AS 'Result'
FROM DeductionTbl
WHERE 
    (EmpID = @empID)
GROUP BY Code, TransDate
ORDER BY TransDate

NOTE:

The SUM(ISNULL(CM,0)) may return NULL when running query on an empty result set also it avoid the ANSI WARNINGS about aggregating NULL.

And ISNULL(SUM(CM),0) is not regarded as nullable.