Magento Category Sales Report Query

category_sales_report.sql

SET @categoryEntityTypeId := (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_category');
SET @nameAttributeId := (select attribute_id FROM eav_attribute where attribute_code = 'name' and entity_type_id = @categoryEntityTypeId);
SET @storeId = 1;
select w.name as website_name, name.value as category_name, SUM(oi.base_row_invoiced) as total,SUM(qty_invoiced) FROM catalog_category_product cp
INNER JOIN sales_flat_order_item oi ON oi.product_id = cp.product_id
INNER JOIN catalog_category_entity_varchar name ON name.entity_id = cp.category_id AND name.store_id = 0 
INNER JOIN core_store s ON s.store_id = oi.store_id
INNER JOIN core_website w ON w.website_id = s.website_id
WHERE name.attribute_id = @nameAttributeId
AND cp.category_id IN ({commaseperatedcategoryids})
#AND oi.store_id= @storeId
AND oi.created_at > DATE_SUB(NOW(), INTERVAL 3 MONTH)
group by cp.category_id,oi.store_id;