Magento Products Ordered Report

Question

I'm struggling with the Magento report for "Products Ordered".

Is the Ordered Quantity affected by the date filter? Why do some of the products show as 0? Where does Magento keep the ordered_qty data? I am using Magento 1.4.0.1.

Answer 1

Magento keeps record of all items sold in sales_order_entity, which you can retrieve like this:

select * from sales_order_entity where 
    entity_type_id = (select entity_type_id from eav_entity_type where 
        entity_type_code = 'sales/order_item'
    );

By grouping by entity_id and counting the result, you can get an accurate total of the quantity_sold for a date range. You can see how Magento does this in Mage_Reports_Model_Mysql4_Order_Collection. From the code, it appears that Magento does in fact respect the date parameter.

Just to be clear, this means that there is no single place where you can retrieve an "ordered_qty" number without more complex queries. The most obvious reason some products show as 0 is that they haven't sold anything.

Answer 2

In v1.4 and up, you should be able to query the sales_flat_order_item table and then group and sum on qty_ordered. e.g.

select product_id, sku, sum(`qty_ordered`) from sales_flat_order_item group by product_id

Cheers, JD