Subtraction Issue

[dvannoy]

I have a query that has an inner join. everything works fine except I'm trying to subtract two fields to get a remaining balance. both fields are int.

SUM(MY_TABLE.TRANSFERED) AS TRANSFERED
,SUM(MY_TABLE.RECEIVED - MY_TABLE.TRANSFERED) AS REMAINING

what is happening is the value will not zero out if it's supposed to be a 0

e.g.

so the sum of RECEIVED = 4074 and the sum of TRANSFERED = 4074 the remaining amout is showing 4074 and not 0

what causes this and how can I fix it ?

any help would be appreciated

thanks

 

[fredericofonseca]

do

SUM(MY_TABLE.TRANSFERED) AS TRANSFERED
,SUM(MY_TABLE.RECEIVED) - sum(MY_TABLE.TRANSFERED) AS REMAINING

But to verify that indeed what you expect is correct do

SUM(MY_TABLE.TRANSFERED) AS TRANSFERED
,SUM(MY_TABLE.RECEIVED) AS RECEIVED
,SUM(MY_TABLE.RECEIVED) - sum(MY_TABLE.TRANSFERED) AS REMAINING

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886