Category Product Sales Report

category_products_sales_report.sql

#category_products_sales_report
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 @categoryId = 1;
select w.name as website_name, oi.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 core_store s ON s.store_id = oi.store_id
INNER JOIN core_website w ON w.website_id = s.website_id
WHERE cp.category_id = @categoryId
AND oi.created_at > DATE_SUB(NOW(), INTERVAL 12 MONTH)
group by oi.product_id,oi.store_id;