Magento Get SUM of order totals between set dates

Question

I can do this with regular mySQL but I would like to be able to do it "the magento way" as it were...

What I would like to do, is run a query which will SUM(grand_total) for my order totals between set dates, ie work out the total revenue from July 2012.

I've tried various variations on this, and I might be really close or I might be a million miles away, so I'd appreciate any help anyone can give me! What I have so far is:

$orders = Mage::getModel('sales/order')->getCollection();

$orders->addAttributeToFilter('date_field', array(
'from' => '2011-09-01',
'to' => '2011-09-30',
));
$orders->addExpressionAttributeToSelect('grand_total', 'SUM({{grand_total}})', grand_total);
$orders_total->getSelect()->$orders->grand_total(SUM(grand_total));

Thank you in advance!

Answer 1

'the magento way' would be using collections.

Your question states all orders since July? If this is the case then you only require the 'from' in the filter and not the 'to'...

$orderTotals = Mage::getModel('sales/order')->getCollection()
    ->addAttributeToFilter('status', Mage_Sales_Model_Order::STATE_COMPLETE)
    ->addAttributeToFilter('created_at', array('from'  => '2012-07-01'))
    ->addAttributeToSelect('grand_total')
    ->getColumnValues('grand_total')
;
$totalSum = array_sum($orderTotals);

// If you need the value formatted as a price...
$totalSum = Mage::helper('core')->currency($totalSum, true, false);

Answer 2

Instead of trying to do that by adding mysql expressions in to the query try the following:

$orders = Mage::getModel('sales/order')->getCollection();
$orders->addAttributeToFilter('created_at', array(
'from' => '2011-09-01',
'to' => '2011-09-30',
))
->addAttributeToSelect('grand_total')
->addAttributeToFilter('status', array('eq'=>'complete'))
;
$grandTotal = 0;

foreach($orders as $order)
{
    $grandTotal += $order->getGrand_total();
}

Here we're getting the collection through and then having magento loop through it and add up the grand totals for each order in the collection.

Note that we changed 'date_field' to 'created_at'. Also you can put all of the collection modifiers on one line.

We've also added in a filter to exclude everything except completed orders. As it was written before, it would count the grand totals from canceled orders as well. If you want to count canceled orders, just delete that line.