SELECT p.name,MIN(pl.quantity) min_order_qty,AVG(pl.quantity) avg_order_qty,vld.qty_available current_stock FROM products p LEFT JOIN variation_location_details vld ON p.id=vld.product_id LEFT JOIN purchase_lines pl ON p.id=pl.product_id LEFT JOIN transactions t ON pl.transaction_id=t.id WHERE p.enable_stock=1 AND t.type="purchase" GROUP BY p.id,p.name,vld.qty_available ORDER BY min_order_qty DESC;