mysql group by and joining issue

Question

I want to know my total stock purchase balace. I have product_stock table and product_purchase_item table.

Product stock table

product_stock_id(int PK)
product_id(int)
product_size(int)
product_size_quantity(int)


Product purchase item

item_id(int PK)
product_id(int)
product_size(int)
pr_pur_cost_price(douple)
quantity

time_create(timestamp)

Note that product_size and product_id are the same and product_stock.product_size_quantity count the total quantity of each product and size. But my product_stock table is store only one row for same product_id and product_size (I use trigger for stock table).For example I purchase a product in three times.

product_purchase_item table show


item_id product_id product_size pr_pur_cost quantity time_create
24      160        1            200         1        2013-09-21 20:18:55
502     160        1            150         4        2014-04-27 15:24:11
503     160        4            180         2        2014-04-27 18:54:45
404     100        2            200         1        2014-04-27 18:54:45   

product_stock` table show

stock_id product_id product_size product_size_quantity
17       160        1            5       
337      160        4            2  
331      100        2            1             

Finally I want a tabular data which will contain product_stock table with average cost price of same product_id and product_size I need this type of data from a query

product_id product_size product_size_quantity average_cost
160        1            5                      175    
160        4            2                      180
100        2            1                      200

here, 1st row's show 175 average_cost because of 160- product_id and 1-product_size contain total pr_pur_cost quantity was 350. I divide by 2 because of 2 rows How can I do it. I was try with group by and join.But I cant . I was try like

select 
    avg(i.pr_pur_cost_price) as cost,s.product_size,s.product_id  
from 
    product_stock as s 
left join 
    product_purchase_item as i
on 
    i.product_id=s.product_id 
and 
    i.product_size=s.product_size 
group by 
    i.product_size 

but it doesnt work

Answer 1

How about something like

SELECT product_id, 
       product_size, 
       SUM(quantity) product_size_quantity, 
       AVG(pr_pur_cost)average_cost
FROM product_purchase_item
GROUP BY product_id, product_size

SQL Fiddle DEMO

If yuo realy have to join to your main table you could try

SELECT product_stock.product_id, 
       product_stock.product_size, 
       product_stock.product_size_quantity, 
       AVG(pr_pur_cost)average_cost
FROM product_stock INNER JOIN
     product_purchase_item 
ON  product_stock.product_id = product_purchase_item.product_id
AND product_stock.product_size = product_purchase_item.product_size
GROUP BY
   product_stock.product_id, 
   product_stock.product_size

SQL Fiddle DEMO