SELECT p.name,AVG(pl.quantity) avg_purchase_qty,AVG(pl.purchase_price) avg_unit_cost FROM products p JOIN purchase_lines pl ON p.id=pl.product_id JOIN transactions t ON pl.transaction_id=t.id WHERE t.type="purchase" AND p.enable_stock=1 GROUP BY p.id,p.name ORDER BY avg_purchase_qty DESC;