How can I use the loanAmount and amountPaid to get the balance.

SELECT (SELECT SUM(loanRepayment.amount) FROM `loanRepayment` WHERE loanRepayment.loanNumber='MMSE22062311' AND loanRepayment.transactionType ='DR')loanAmount,
(SELECT SUM(loanRepayment.amount) FROM `loanRepayment` WHERE loanRepayment.loanNumber='MMSE22062311' AND loanRepayment.transactionType ='CR')amountPaid,

(loanAmount-amountPaid)balance

CodePudding user response：

SELECT SUM(CASE WHEN transactionType = 'DR' 
                THEN amount
                ELSE 0
                END) loanAmount,
       SUM(CASE WHEN transactionType = 'CR' 
                THEN amount
                ELSE 0
                END) amountPaid,
       SUM(CASE WHEN transactionType = 'DR' 
                THEN amount
                ELSE -amount
                END) balance
FROM loanRepayment
WHERE loanNumber='MMSE22062311' 
  AND transactionType IN ('DR', 'CR')