SELECT bl.name location,COUNT(DISTINCT p.id) total_products,COUNT(DISTINCT CASE WHEN vld.qty_available>0 THEN p.id END) stocked_products FROM business_locations bl JOIN products p CROSS JOIN variation_location_details vld ON p.id=vld.product_id AND bl.id=vld.location_id WHERE p.enable_stock=1 GROUP BY bl.id,bl.name ORDER BY stocked_products DESC;