SELECT c.name category,COUNT(DISTINCT p.id) product_count,AVG(vld.qty_available) avg_stock_level,SUM(tsl.quantity) total_sales FROM categories c JOIN products p ON c.id=p.category_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 AND t.type="sell" GROUP BY c.id,c.name ORDER BY total_sales DESC;