Query for retrieving statewise sales summary from Magento Database

statewise_sales_summary.sql

SET @countryId = 'US'; 
SET @storeId = 7;
SELECT a.region,SUM(s.grand_total) AS total,COUNT(*) AS no_of_orders FROM sales_flat_order s 
INNER JOIN  sales_flat_order_address a  ON a.entity_id = s.shipping_address_id
WHERE s.created_at >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
AND a.address_type = 'shipping'
AND s.store_id = @storeId
AND a.country_id = @countryId
GROUP BY a.region
ORDER BY total desc