outer apply: negative SUM that must be positive

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

outer apply: negative SUM that must be positive

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-02-16 : 12:09:33

Modifying a query that Visakh posted and that I addapted to my needs, it returns exactly the records I expect... but fails on an aggregate function: The sum it returns MUST be positive, but is returned negative!??!

select t.voucher, t0.SUM_OFFSET, t.amountmst
from custtrans t
outer apply
(select SUM(OFFSET.Settleamountmst) AS SUM_OFFSET
from dbo.CUSTSETTLEMENT AS OFFSET
WHERE OFFSET.OFFSETRECID = t.RECID
GROUP BY OFFSET.OFFSETRECID) t0
WHERE t.recid='5637300646'

If I manually look for Settleamountmst for that record I get: 7019.01 and 669.61 which sums 7688.62 and not -7688.62. Does outer apply turns positive into negative and negative into positive?

Confused, Martin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-16 : 15:33:49

Nope. it wont do that sign change, it will just aggregate it
perhaps, the actual quantities were negative. what does this return?


select t.voucher, t0.SUM_OFFSET, t.amountmst
from custtrans t
outer apply
(select SUM(ABS(OFFSET.Settleamountmst)) AS SUM_OFFSET
from dbo.CUSTSETTLEMENT AS OFFSET
WHERE OFFSET.OFFSETRECID = t.RECID
GROUP BY OFFSET.OFFSETRECID) t0
WHERE t.recid='5637300646'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-02-17 : 03:39:11

1 + 1 still is 2... sorry, my fault. I had checked it a million times, but didn't noticed that I was referring to a wrong column. Mixed up two ID's (OFFSETRECID & TRANSRECID) without noticing it, because the realtionship worked and the numbers it threw where correct... just the sign wasn't.

Have a nice Weekend,
Martin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-17 : 12:58:40

no problem
glad that it got sorted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources