CODEWITHSUNDEEP
sqlsql-server
Olajumoke Ademilade
Olajumoke Ademilade

Reputation: 191

How Can I SELECT query with CASE condition and SUM()

Table structure enter image description here

I'm currently using these sql statements. My table has the field trx_type which contains "LD" or "LP". I can sum up the amount of principal by executing 2 SQL statements as shown below.

select sum(principal) from loanhist where trx_type ='LD' and ac_no  = '101300' and cycle = 1  --150000
select sum(principal) from loanhist where trx_type ='LP' and ac_no  = '101300' and cycle = 1--- 73512.13

Which if I subtract LD from LP I get 76,487.87

When I tried to use CASE condition this way:

-SUM(CASE WHEN trx_type='LD' THEN LoanHist.principal ELSE 0 END) -
SUM(CASE WHEN trx_type='LP' THEN LoanHist.principal ELSE 0 END) AS outstanding_balance

I got this -229,463.61.

Since LD has a negative sign, if I should remove the negative sign from SUM it adds the digits instead of subtracting.

Can someone please suggest a better way of solving this!!

Upvotes: 1

Views: 108

Answers (1)

Aaron Dietz
Aaron Dietz

Reputation: 10277

You could use ABS() to get the absolute (positive) value for the sake of making your subtraction simple:

SUM(CASE WHEN trx_type='LD' THEN ABS(LoanHist.principal) ELSE 0 END)

Upvotes: 1

Related Questions