SELECT & Calculate SQL but answer wrong

Question

I want to select data from mysql. And also wanted to do some calculation at query excution time. I had two sql tables:

Loan Table :

loan_id|Customer_name|Total_amount
1000   |Sashika      |55000
1001   |Amell        |11000

Settlement Table :

Sett_id|Loan_id|Amount
a123    |1000    |1000
b123    |1001    |2000

Thats how my two tables look like. I want to get due value of loans. I used below code to generate but it wont' show correct values.

select loan.loan_id,loan.customer_name,loan.total_amount,sum(settlement.amount) as 'Total Received',
      ((loan.total_amount)-sum(settlement.amount ))as 'Total Due'
from loan , settlement
where loan.loan_id = 1001;

This code get value of total(column) of settelement table. When I try with another loan id(where loan_id=) also amount of total of settlement value show and calculate. But It isn't the value I want. Please help me guys

Loan_ID| Customer_Name|Total_Amount|Total_Received|Total_Due
1000       |Sashika       |55000       |1000          |54000

This is How I want Thanks for edit my question and make it more understandable :)

Answer 1

You should use inner join and group by

select 
    loan.loan_id
   ,loan.customer_name
   ,loan.total_amount
   ,sum(settlement.amount) as 'Total Received'
   ,((loan.total_amount)-sum(settlement.amount ))as 'Total Due' 
from loan 
inner join settlement  on  loan.loan_id =  settlement.loan_id
where loan.loan_id=1001
group by load.loan_id

Answer 2

I would recommend a query like this:

select l.*, (l.total_amount - s.amount) as amountDue
from loans l left join
     (select loan_id, sum(amount) as amount
      from settlement s
      group by loan_id
     ) s
     on l.loan_id = s.loan_id;

You have numerous problems with your query:

• You are using aggregation functions but have no group by.
• You have both aggregated and non-aggregated columns in the select.
• You are using the ancient form of join syntax. Simple rule: Never use commas in the from clause.

I would also recommend that you not use single quotes for column aliases. The proper escape character in MySQL is the backtick. Use single quotes only for string and date constants.

Answer 3

Try, Inner join wont work if there is no settlement at ID 1001, it will show 0 rows, to display total received as null I have changed inner to left join

select loan.loan_id, loan.customer_name, loan.total_amount, 
sum(settlement.amount) as ''Total Received',
((loan.total_amount)-sum(settlement.amount )) as 'Total Due'
from load
left join settlement
on settlement.loan_id = loan.loan_id
where loan.loan_id = 1001;

Answer 4

Here's my version:

select 
    loan.loan_id,
    loan.customer_name,
    loan.total_amount,
    ifnull(sum(settlement.amount), 0) as 'Total Received',
    ((loan.total_amount) - ifnull(sum(settlement.amount), 0)) as 'Total Due' 
from loan
left join settlement on
    settlement.loan_id = loan.loan_id
where 
    loan.loan_id=1000

You need a LEFT JOIN, not an inner join to handle the case where no payments have been made yet. You also need to make sure sum(settlement.amount) does not return NULL when there are no entries in the settlement table, otherwise the 'Total Due' will also be NULL.

You don't actually need a GROUP BY like Gordon said, if you are only taking one load at a time.