Mysql left join and sum

Question

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

Answer 1

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