SELECT bl.name location,COUNT(DISTINCT p.id) product_count,SUM(vld.qty_available) total_stock,SUM(tsl.quantity) monthly_sales FROM business_locations bl JOIN variation_location_details vld ON bl.id=vld.location_id JOIN products p ON vld.product_id=p.id LEFT JOIN transaction_sell_lines tsl ON p.id=tsl.product_id LEFT JOIN transactions t ON tsl.transaction_id=t.id AND t.type="sell" AND t.transaction_date>=DATE_SUB(CURRENT_DATE,INTERVAL 30 DAY) GROUP BY bl.id,bl.name ORDER BY monthly_sales DESC;