I'm trying to sum two columns, and subtract the two sums to get a balance amount of two columns. But I'm getting the same output for both queries even though I know there are negative values. I need to exclude those negative values in the query.
我正在尝试将两列相加,并减去两个总和以获得两列的余额。但即使我知道有负值,我也会得到两个查询的相同输出。我需要在查询中排除那些负值。
SELECT SUM(ReceiptAmt) - SUM(BalanceAmt) FROM Receipt
SELECT SUM(ReceiptAmt) - SUM(BalanceAmt) FROM Receipt WHERE ReceiptAmt > 0.00
Any ideas on how to exclude the negative values? Or even a different approach to this.
关于如何排除负值的任何想法?或者甚至采用不同的方法。
2 个解决方案
#1
this may be what you're after:
这可能就是你所追求的:
SELECT
SUM(case when ReceiptAmt<0 then 0 else ReceiptAmt end) -
SUM(case when BalanceAmt<0 then 0 else BalanceAmt end)
FROM Receipt
#2
Should the BalanceAmt be in the where clause also?
BalanceAmt是否也应该在where子句中?
WHERE ReceiptAmt > 0.00
AND BalanceAmt > 0.00
#1
this may be what you're after:
这可能就是你所追求的:
SELECT
SUM(case when ReceiptAmt<0 then 0 else ReceiptAmt end) -
SUM(case when BalanceAmt<0 then 0 else BalanceAmt end)
FROM Receipt
#2
Should the BalanceAmt be in the where clause also?
BalanceAmt是否也应该在where子句中?
WHERE ReceiptAmt > 0.00
AND BalanceAmt > 0.00