Q:

Sales per category SQL

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

A:

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

