Count products and sum prices from different tables

I have these three tables:

Products
ID      NAME
1       Notebook
2       Keyboard

Base1
PRODUCT_ID    PRICE
1             2
1             2
1             2
2             3

Base2
PRODUCT_ID    PRICE
1             2
1             2
1             2
1             2
2             3
2             3  

What I need is to get a list of all products with 开发者_Go百科number of occurences and sum proces, like this:

ID     NAME       Base1_count  Base1_SumPrice     Base2_count  Base2_SumPrice
1      Notebook   3            6                  4            8
2      Keyboard   1            3                  2            6

How to write proper MySQL query?

---

Hey You can get answer by following query

SELECT product_base1.product_id ID
, product_base1.product_name NAME
, product_base1.base1_count Base1_count
, product_base1.base1_price Base1_SumPrice
, COUNT(b2.id) Base2_count
, SUM(b2.price) Base2_SumPrice
FROM ( 
SELECT p.id product_id 
        , p.name product_name
        , COUNT(b.id) base1_count
        , SUM(b.price) base1_price
FROM products p
LEFT OUTER
JOIN base1 b ON p.id = b.id
GROUP BY p.id 
    ) 
    product_base1
LEFT OUTER
JOIN base2 b2 ON b2.id = product_base1.product_id
GROUP BY product_base1.product_id

---

SELECT p.ID, p.NAME,
     sum(CASE WHEN b.Base_Name = 'Base1' THEN 1 ELSE 0 END) Base1_count,
     sum(CASE WHEN b.Base_Name = 'Base1' THEN b.PRICE ELSE 0 END) Base1_SumPrice,
     sum(CASE WHEN b.Base_Name = 'Base2' THEN 1 ELSE 0 END) Base1_count,
     sum(CASE WHEN b.Base_Name = 'Base2' THEN b.PRICE ELSE 0 END) Base1_SumPrice
  FROM Products p
  LEFT JOIN (SELECT 'Base1' Base_Name, b1.*
             UNION ALL
             SELECT 'Base2' Base_Name, b2.*) b
         ON p.ID=b.PRODUCT_ID
GROUP BY p.ID, p.Name