I have two tables

1. Loan

2. Repayment Schedule

   loan: id is primary key
   
   id, closedon_date
   -
   1,  2022-05-01
   2,  2022-06-01
   

   repayment_schedule: loan_id is Foreign key
   
   id, loan_id, principal_amount, due_date
   -
   1,  1,        100              2022-05-01  
   2,  1,        100              2022-06-01
   3,  2,        200              2022-05-01  
   4,  2,        200              2022-06-01
   5,  2,        200              2022-07-01
   

   I want to write a select SQL query that returns the sum of the principal amount of each loan_id with the condition that the due date is less or equal to the closed-on date.
   Output:

   loan_id, sum(principal_amount)
   -
   1,        100
   2,        400    
   

This is the closet approximation to the query

select loan_id, sum(principal_amount) from repayment_schedule
where
  repayment_schedule.due_date <= loan.closedon_date
group by loan_id

CodePudding user response：

Try this:

SELECT loan_id, sum(principal_amount) 
FROM repayment_schedule s
INNER JOIN loan L
    ON s.[loan_id] = L.[id]
WHERE s.due_date <= l.closedon_date
GROUP BY loan_id

CodePudding user response：

You're almost there

select loan_id, sum(principal_amount) from repayment_schedule
JOIN loan ON loan.id = repayment_schedule.loan_id
where
  repayment_schedule.due_date <= loan.closedon_date
group by loan_id

Just JOIN with table loan