sum and join the column correctly-CodePudding

database

loan

[id_loan] [id_customer] [qty_loan][amount] 
  1            1             2       8 
  2            1             1       4 
  3            2             3      12 
  4            1             1       4

payment

[id_pay] [id_customer] [qty_pay] [pay]
  1            1           1       4 
  2            1           2       8 
  3            2           2       8 
  4            2           1       4

i want to join and then get sum result but the result was wrong here is the sql code

SELECT
     SUM(qty_loan) as qty_loan,SUM(amount) as amount ,SUM(qty_pay) as qty_pay,SUM(pay) as pay  
    FROM loan JOIN payment ON loan.id_customer = payment.id_customer 
    WHERE loan.id_customer='1'

CodePudding user response：

When we have 2 records in both tables the JOIN finds 4 matches and each value is duplicated.
We can query one of the tables in a sub-query, grouped by id_customer, to avoid this problem.

SELECT
  l.id_customer,
  qty_loan,
  amount ,
  SUM(qty_pay) as qty_pay,
  SUM(pay) as pay  
FROM (SELECT 
        id_customer,
        SUM(qty_loan) as qty_loan,
        SUM(amount) as amount 
        FROM loan 
        GROUP BY id_customer) l
LEFT JOIN payment 
ON l.id_customer = payment.id_customer 
/*-WHERE loan.id_customer='1'*/
GROUP BY 
  l.id_customer,  
  qty_loan,
  amount ;

id_customer | qty_loan | amount | qty_pay | pay
----------: | -------: | -----: | ------: | --:
          1 |        4 |     16 |       3 |  12
          2 |        3 |     12 |       3 |  12

db<>fiddle here