Geek Answers Handbook

Magento Get SUM from order totals between set dates

I can do it using regular mySQL, but I would like to be able to do it "purple" as it were ...

What I would like to do, a query is launched that will be SUM (grand_total) for my number of orders between the set dates, that is, to work out the total income from July 2012.

I tried various variations of this, and I could be very close, or I could be millions of miles, so I would appreciate any help anyone could give me! What I still have:

$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!

+4
source share
2 answers

'purple path' will use collections .

Does your question indicate all orders from July? If so, then you only need "from" in the filter, not "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);
+7
source

Instead of trying to do this by adding mysql expressions 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 collect the collection, and then fill it with a purple loop and add up the grandiose results for each order in the collection.

Notice that we changed 'date_field' to 'created_at' . You can also put all collection modifiers on one line.

We also added a filter to exclude everything except completed orders. As previously written, he would also take into account the grandiose results from canceled orders. If you want to cancel canceled orders, just delete this line.

+2
source

More articles:


All Articles