CODEWITHSUNDEEP
mysql
user2209076
user2209076

Reputation: 1

Summing Records in 2 MySQL fields From Same Table and Use It As a Condition

I am working on a project, and part of it requires me to be able to display records of individuals who have completed repayment. To complete repayment they must have made monthly repayments and a lump sum. So I want to be able to sum the records in an individual's repayment amount field and lumpsum field, indentified by his loanid. Here is the sample table

Repayment table

loanid  |  repayment_amount  | lumpsum  | memberid
  21             20,000           30,000      45

loan table

loanid  |  amountdue  | memberid
  21        40,000         45

Member table 

memberid  |  first_name  | middle_name  | last_name
  21             John        Tom            Bun

Here is an SQL query I have written in MySQL

SELECT first_name, last_name
FROM repayment,loan, members
WHERE repayment.loanid = loan.loanid AND repayment.memberid = members.memberid 

WHERE (SUM(amount)+ SUM(lumpsum)) >= loan.amountdue

I can't seem to get this to display what I want, a list of folks where the sum of their amount and lumpsum is greater than the amount due.

Kindly help

Upvotes: 0

Views: 90

Answers (2)

David Jashi
David Jashi

Reputation: 4511

SELECT first_name, last_name
FROM repayment,loan, members
WHERE
 repayment.loanid = loan.loanid AND 
 repayment.memberid = members.memberid 
GROUP BY first_name, last_name
HAVING
 SUM(repayment.repayment_amount) + SUM(repayment.lumpsum) >= SUM(loan.amountdue)

Upvotes: 1

Deepika
Deepika

Reputation: 826

SELECT m.first_name, m.last_name
FROM repayment r,loan l, members m
WHERE r.loanid = l.loanid AND r.memberid = m.memberid 
HAVING (SUM(r.repayment_amount)+ SUM(r.lumpsum)) >= l.amountdue

Upvotes: 0

Related Questions