I run Magento 1.9.4 and need to have some numbers on a custom dashboard. I need a SQL query to get amount of sales from a specific category. And also the sales this month from same category. I need to do this from a SQL query because the Dashboard is an external site on same server with DB access
If anyone need something like this I found a solution.
select cp.category_id, cp.product_id, i.name, count(*) as count, sum(i.base_price) as total_price from sales_flat_order_item i join catalog_category_product cp on i.product_id = cp.product_id where cp.category_id = '$CategoryIDGoesHere' group by cp.product_id
This will output all the products with the number of sales and total price in the defined category