Thursday, April 28, 2011

Sum negative values in Money column

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.

From stackoverflow
  • Should the BalanceAmt be in the where clause also?

    WHERE ReceiptAmt > 0.00 
          AND BalanceAmt > 0.00
    
  • 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

    Tim Meers : This works by replacing the values rather than adding a WHERE clause. It also helped me identify that it's my problem. The negative values are negative the same is *both* columns! So of course it's going to return the same result frmo both of my queries. Thanks for the help.

0 comments:

Post a Comment