4

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!

Sam Stones
  • 73
  • 2
  • 6
  • FYI, I ended up making an order export tool via collections and many orders are omitted, which I found while checking against raw MySql queries. "The Magento way" imho is not the correct way. – Krista K Jun 19 '13 at 20:26

2 Answers2

7

'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);
Drew Hunter
  • 10,136
  • 2
  • 40
  • 49
  • Thanks for the reply, the error message I get with this is: Fatal error: Call to a member function getModelInstance() on a non-object in /public_html/app/Mage.php on line 432 – Sam Stones Aug 10 '12 at 11:25
  • Ok, I figured it out, I hadn't initialised Mage::app(); in the right place! It looks like something has worked anyway, so thank you again. – Sam Stones Aug 10 '12 at 11:30
  • I know this is old but is there a way to filter by SKU or ID? – Mike Tim Turner Nov 14 '16 at 13:21
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.

Ben
  • 224
  • 1
  • 10