对一列求和,然后对另一列求和 [英] SUM a column then SUBTRACT to the SUM of another column
问题描述
我有一个包含以下字段的表:
I have a table that has this fields:
EmpID,
Code,
Amount,
TransDate,
CM,
CMDate
我想做什么进入数据网格,就是将所有 Amount
SUM
都具有相同的 Code
和 SUM
CM
具有相同的代码
作为 Amount
,然后将 SUM(Amount)
减去到 SUM(CM )
,但如果结果为 NULL
(例如,没有SUM并减去的 CM
到 SUM(Amount)
),则只需选择 SUM(Amount)
。我想出了以下语句:
What I want to get to a datagrid, is to SUM
all the Amount
that has the same Code
and SUM
the CM
that has the same Code
as the Amount
then subtract the SUM(Amount)
to the SUM(CM)
but if the Result IS NULL
(e.g. there is no CM
to SUM and Subtract to the SUM(Amount)
) then it just have to select the SUM(Amount)
. I came up with this statement:
SELECT
TransDate,
Code,
SUM(Amount) - SUM(CM) AS 'Result'
FROM DeductionTbl
WHERE
(EmpID = @empID)
GROUP BY Code, TransDate
ORDER BY TransDate
如果有 SUM(CM)$ c,我得到结果值$ c>减去
具有相同的 SUM(Amount)
,但是如果没有<$,我得到结果 NULL
值c $ c> SUM(CM)代码
。我不想在数据网格上使用 NULL
值。如果存在 NULL
值,则返回 SUM(Amount)
。有没有办法做到这一点?谢谢!
I get the Result value if there is a SUM(CM)
to subtract to the SUM(Amount)
but I get the Result NULL
value if there is no SUM(CM)
of the same Code
. I don't want the NULL
value on the datagrid. If there is a NULL
value just return the SUM(Amount)
. Is there a way to do that? Thanks!
推荐答案
使用 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
注意:
SUM(ISNULL(CM,0))
可能返回 NULL
在
空结果集上运行查询时,还避免了 ANSI警告
关于汇总NULL
。
和 ISNULL(SUM(CM),0)
不被视为可为空。
这篇关于对一列求和,然后对另一列求和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!