Search code examples
mysqljoinsum

sum and join the column correctly


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'

Solution

  • 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