SELECT p.name,vld.qty_available current_stock,v.default_purchase_price unit_value,vld.qty_available*v.default_purchase_price total_value,COUNT(DISTINCT t.id) transaction_count FROM products p JOIN variations v ON p.id=v.product_id LEFT JOIN variation_location_details vld ON p.id=vld.product_id LEFT JOIN transaction_sell_lines tsl ON p.id=tsl.product_id LEFT JOIN transactions t ON tsl.transaction_id=t.id WHERE p.enable_stock=1 GROUP BY p.id,p.name,vld.qty_available,v.default_purchase_price ORDER BY total_value DESC;