SELECT c.name category,COUNT(DISTINCT p.id) product_count,COUNT(DISTINCT v.id) variation_count,AVG(vld.qty_available) avg_stock_level FROM categories c JOIN products p ON c.id=p.category_id LEFT JOIN variations v ON p.id=v.product_id LEFT JOIN variation_location_details vld ON p.id=vld.product_id WHERE p.enable_stock=1 GROUP BY c.id,c.name ORDER BY variation_count DESC;