Magento Sales Reports – Sales Revenue Per Product from Sales/Order

collection;filtermagento-1.9reportssales

I need to get the sales revenue per product using sales order.

I have been using product_collection to multiply FinalPrice with order_qty
But as the FinalPrice changes, this is no good to work out the actual sales revenue.

I can get the revenue for all products but I cannot seem to filter by Product ID.

$revenue = Mage::getModel( 'sales/order' )->getCollection()
            ->addAttributeToSelect('grand_total')
            ->getColumnValues('grand_total');

$overall_profit = array_sum( $revenue );
$overall_profit = Mage::helper( 'core' )->currency( $overall_profit, true, false );

I am using this inside a loop so I have access to Product_ID, SKU etc

Best Answer

Based on your own answer, you can do it using Magento objects as Julien suggested:

$collection = Mage::getResourceModel('sales/order_item_collection')
    ->addFieldToFilter('product_id', $prodId);

$collection->getSelect()
    ->from('sales_flat_order_item', 
         array('sum' => 'sum(sales_flat_order_item.row_total_incl_tax)'
    );

$productRevenue= 0;

foreach($collection as $result) {
     $productRevenue += $result->getSum();
}

$productRevenue = $productRevenue / 2;
Related Topic