We help IT Professionals succeed at work.

Adding mutilple fields

I have 6 fields.

Field1
Field2
Field3
Field4
Withdraw
Contributions

If any of the first 4 are negative I need to add them up with the -Abs(Withdraw) and call it Withdraw.

If any of the first 4 are positive I need to add them up with Contributions and call it Contributions.

*Note, the fields may be zeros or NULLs also

The final select statement just needs to be
Select
Withdraw,Contributions from balances.

TIA
Comment
Watch Question

Top Expert 2011

Commented:
select case when field1 > 0 or field2 > 0 or field3 > 0 or field4 > 0
    then field1+field2+field3+field4 end +contributions as contributions
,case when field1 < 0 or field2 < 0 or field3 < 0 or field4 < 0
    then field1+field2+field3+field4 end - withdraw as withdraw
from (
select coalesce(field1,0) as field1
,coalesce(field2,0) as field2
,coalesce(field3,0) as field3
,coalesce(field4,0) as field4
from yourtable
) as x
How about retrieving the data into a client data set, and then doing something like this (my sample is delphi, but can do the same w/ any):

while not eof do begin
  if fieldByName('field1).asFloat <> 0
    then if fieldByName('field1).asFloat < 0 then fieldByName('withdraw').asFloat := fieldByName('withdraw').asFloat + abs(fieldByName('field1).asFloat)
           else fieldByName('contributions').asFloat := fieldByName('contributions').asFloat + fieldByName('field1).asFloat;
... // address fields 2..4
// would really put the above in a procedure or loop depending on context so I didn't actually duplicate this code
  next;
end;
// then apply the updates to the table
// either do your final query now or use the in memory cds to get totals/details for contributions/withdrawals
Lowfatspread: Doesn't your approach result in a problem if there are a mix of positive and negative values, e.g, field 1 is 10 and field2 is -20... unless I'm misreading, since field1 is > 0, -10 will be put into contributions instead of 10 into contributions and -20 into withdrawals? (Not trying to criticize your answer btw, but to understand it.... since it's clear to me that you knows stuff that I don't... i stumbled into SQL by accident and still have some serious gaps.)
Scott PletcherSenior DBA
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:

SELECT
    CASE WHEN SIGN(Field1) = -1 THEN ABS(Field1) ELSE 0 END +
    CASE WHEN SIGN(Field2) = -1 THEN ABS(Field2) ELSE 0 END +
    CASE WHEN SIGN(Field3) = -1 THEN ABS(Field3) ELSE 0 END +
    CASE WHEN SIGN(Field4) = -1 THEN ABS(Field4) ELSE 0 END AS Withdraw,
    CASE WHEN SIGN(Field1) >= 0 THEN Field1 ELSE 0 END +
    CASE WHEN SIGN(Field2) >= 0 THEN Field2 ELSE 0 END +
    CASE WHEN SIGN(Field3) >= 0 THEN Field3 ELSE 0 END +
    CASE WHEN SIGN(Field4) >= 0 THEN Field4 ELSE 0 END AS Contributions
FROM tablename
...

Open in new window

Top Expert 2011

Commented:
@eric you maybe correct i took the askers requirements as they read...

any positive add the 4 fields ...
any negative add the 4 fields...

Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
If you need to add the fields to exist Withdraw and Contributions amounts, you can do this:
SELECT
    ABS(Withdraw) +
    CASE WHEN SIGN(Field1) = -1 THEN ABS(Field1) ELSE 0 END +
    CASE WHEN SIGN(Field2) = -1 THEN ABS(Field2) ELSE 0 END +
    CASE WHEN SIGN(Field3) = -1 THEN ABS(Field3) ELSE 0 END +
    CASE WHEN SIGN(Field4) = -1 THEN ABS(Field4) ELSE 0 END AS Withdraw,
    Contributions +
    CASE WHEN SIGN(Field1) >= 0 THEN Field1 ELSE 0 END +
    CASE WHEN SIGN(Field2) >= 0 THEN Field2 ELSE 0 END +
    CASE WHEN SIGN(Field3) >= 0 THEN Field3 ELSE 0 END +
    CASE WHEN SIGN(Field4) >= 0 THEN Field4 ELSE 0 END AS Contributions
FROM tablename
...

Open in new window