Mysql left join and sum

left-join, mysql, sum

Solution

Here is an easy way for your desired result

SELECT
  i.id,
  p.amount,
  pa.amount
FROM items AS i
  LEFT JOIN (SELECT
           id,
           item_id,
           SUM(amount)    amount
         FROM payments
         GROUP BY item_id) AS p
    ON p.item_id = i.id
  LEFT JOIN (SELECT
           payment_id,
           sUM(amount)    amount
         FROM payment_actions) AS pa
    ON pa.payment_id = p.id
group by i.id

Problem

I have three table and i need sum amount separately. Also i will use fields on select or where clause. ``` SELECT i.*, x.* FROM items AS i LEFT JOIN ( SELECT p.item_id ,SUM(p.amount) AS saleAmount ,SUM(IF(p.type=1,pa.amount,0)) AS paidAmount FROM payments AS p LEFT JOIN payment_actions AS pa ON pa.payment_id=p.id GROUP BY p.id ) AS x ON x.item_id=i.id ``` items table; ``` id --- 1 ``` payments table; ``` id | item_id | amount --------------------------- 1 | 1 | 300 ``` payment_actions table; ``` id | payment_id | amount --------------------------- 1 | 1 | 100 1 | 1 | 50 ``` The result should be; ``` saleAmount | paidAmount -------------------------- 300 | 150 ```

Original source