0

I have a a column of data that has both Positive and Negative numbers. I need SQL to find and add only the positive values.

I am trying to add up Total Credits (Negative Number) and Total Debits (Positive Number) for a JE. So I have a single column (AMOUNT) that has both Positive and Negative amounts. In my TOTALCREDIT field I need code to tell it find and sum only the Positive numbers from the AMOUNT column and for the TOTALCREDIT field I need to find and add only the Negative numbers from the AMOUNT column. I have tried variations of the following but it always returns 0.

TOTALDEBIT

sum (case when AMOUNT >= 0 then AMOUNT else 0 end) as positive

TOTALCREDIT

sum (case when AMOUNT < 0 then AMOUNT else 0 end) as negative

So if the AMOUNT column has 25, -25, 30, -30

  • The TOTALDEBIT field will sum to 55
  • The TOTALCREDIT Field will sum to -55

So I need 1 of string code of for each field.

Improve this question
3
  • 2
    The parts of code you posted are correct. Post the full statement. Commented Apr 23, 2019 at 23:26
  • Instead of doing minus, can you do it directly sum (case when AMOUNT < 0 then AMOUNT else 0 end) as negative for credit ? Commented Apr 23, 2019 at 23:47
  • I tried that and it still returned 0. I was thinking that maybe a I could have it SELECT all the positive values and then sum. Or maybe create an if statement that says SUM(IF AMOUNT is < 0 THEN AMOUNT else AMOUNT*0) that way it would turn all positives into 0. I am just not familiar enough with SQL to write something like that. Commented Apr 24, 2019 at 0:04

1 Answer 1

Reset to default
0

I would join to itself

Transaction_Table =

  • transaction_id
  • customer_id
  • Amount

Select

  • x.customer_id
  • SUM(cred.Amount) as TOTALCREDIT
  • SUM(deb.Amount) AS TOTALDEBIT

FROM Transaction_Table x

LEFT JOIN Transaction_Table cred on x.transaction_id = cred.transaction_id and amount < 0

LEFT JOIN Transaction_Table deb on x.transaction_id = cred.transaction_id and amount >= 0

Group by x.customer_id

Or if you are looking the totals across the whole table just

Select

  • SUM(cred.Amount) as TOTALCREDIT
  • SUM(deb.Amount) AS TOTALDEBIT

FROM Transaction_Table x

LEFT JOIN Transaction_Table cred on x.transaction_id = cred.transaction_id and amount < 0

LEFT JOIN Transaction_Table deb on x.transaction_id = cred.transaction_id and amount >= 0

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.