# Adding minus sign to transaction amount if debit type

• ### Question

• User-34860367 posted

Hi all,

I have a query

```SELECT Transaction_No, Transaction_Type, Amount
FROM TransactionTable
```

The above data result will be:

`Transaction_No | Transaction_Type | Amount 1, debit, 10002, credit, 60003, debit, 20004, credit, 340005, credit, 670006, credit, 45007, credit, 450008, credit, 790009, debit, 540010, debit, 200...`
`I need another query to produce the following data`
`Transaction_No | Transaction_Type | Amount | AmountwithSign | Sum(AmountwithSign)1, debit, 1000, -10002, credit, 6000, 60003, debit, 2000, -20004, credit, 34000, 340005, credit, 67000, 670006, credit, 4500, 45007, credit, 45000, 450008, credit, 79000, 790009, debit, 5400, -540010, debit, 200, -200...Basically, I have a Transaction table with above column field data and need to add another column on the fly based on transaction type, then calculate the Sum based on new added column. Thanks in advance.`
Wednesday, August 12, 2015 1:27 PM

• User269602965 posted

CASE

WHEN TRANSACTION_TYPE = 'debit' THEN AMOUNT * -1

WHEN TRANSACTION_TYPE = 'credit' THEN AMOUNT

END AS AMOUNT_WITHSIGN

as far as SUM goes,

specify type of summing.

is this CUMULATIVE SUM row by row?

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Wednesday, August 12, 2015 2:03 PM
• User-219423983 posted

Hi avt2k7,

calculate the Sum based on new added column

The following result is my understanding, if I’m wrong, please let me know.

1, debit, 1000, -1000,-1000
2, credit, 6000, 6000,5000
3, debit, 2000, -2000,3000
4, credit, 34000, 34000,37000

If the result above is what you want, you could refer to the following code, I have tested it on my client and it works.

```select Transaction_No,Transaction_Type,Amount,
case
when  [Transaction_Type ]= 'debit' then [Amount ]*-1 else [Amount ] end as 'AmountwithSign'
into #temp
from dbo.TransactionTable

select t1.Transaction_No,t1.Transaction_Type,t1.Amount,t1.AmountwithSign,sum(t2.AmountwithSign)

from #temp t1, #temp t2

where t1.Transaction_No >= t2.Transaction_No
group by t1.Transaction_No,t1.Transaction_Type,t1.Amount,t1.AmountwithSign
Order by t1.Transaction_No

drop table #temp
```

I hope it’s useful to you.

Best Regards,

Weibo Zhang

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Thursday, August 13, 2015 7:01 AM

### All replies

• User269602965 posted

CASE

WHEN TRANSACTION_TYPE = 'debit' THEN AMOUNT * -1

WHEN TRANSACTION_TYPE = 'credit' THEN AMOUNT

END AS AMOUNT_WITHSIGN

as far as SUM goes,

specify type of summing.

is this CUMULATIVE SUM row by row?

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Wednesday, August 12, 2015 2:03 PM
• User-219423983 posted

Hi avt2k7,

calculate the Sum based on new added column

The following result is my understanding, if I’m wrong, please let me know.

1, debit, 1000, -1000,-1000
2, credit, 6000, 6000,5000
3, debit, 2000, -2000,3000
4, credit, 34000, 34000,37000

If the result above is what you want, you could refer to the following code, I have tested it on my client and it works.

```select Transaction_No,Transaction_Type,Amount,
case
when  [Transaction_Type ]= 'debit' then [Amount ]*-1 else [Amount ] end as 'AmountwithSign'
into #temp
from dbo.TransactionTable

select t1.Transaction_No,t1.Transaction_Type,t1.Amount,t1.AmountwithSign,sum(t2.AmountwithSign)

from #temp t1, #temp t2

where t1.Transaction_No >= t2.Transaction_No
group by t1.Transaction_No,t1.Transaction_Type,t1.Amount,t1.AmountwithSign
Order by t1.Transaction_No

drop table #temp
```

I hope it’s useful to you.

Best Regards,

Weibo Zhang

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Thursday, August 13, 2015 7:01 AM