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

  • This doesn't work for me. I had to add i.name to the group by clause – jscar Jul 12 '18 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.