If you need only "complete" status like me, then just add . ' AND o.status LIKE "complete" '
after . ' WHERE o.customer_id = e.entity_id ';
I've also added the total Money spent by the customer (so the total of all of it's complete orders)
$total_pricesql ='SELECT sum(o.base_grand_total)'
. ' FROM ' . Mage::getSingleton('core/resource')->getTableName('sales/order') . ' AS o'
. ' WHERE o.customer_id = e.entity_id ' . ' AND o.status LIKE "complete" ';
$expr = new Zend_Db_Expr('(' . $total_pricesql . ')');
$collection->getSelect()->from(null, array('total_price'=>$expr));
and later in the same file
$this->addColumn('total_price', array(
'header' => Mage::helper('sales')->__('Total'),
'index' => 'total_price',
'type' => 'number',
'filter' => false,
));
so change @Amasty's first codes:
protected function _prepareCollection() {
$collection = Mage::getResourceModel('customer/customer_collection')
->addNameToSelect()
->addAttributeToSelect('email')
->addAttributeToSelect('created_at')
->addAttributeToSelect('group_id')
->joinAttribute('billing_postcode', 'customer_address/postcode', 'default_billing', null, 'left')
->joinAttribute('billing_city', 'customer_address/city', 'default_billing', null, 'left')
->joinAttribute('billing_telephone', 'customer_address/telephone', 'default_billing', null, 'left')
->joinAttribute('billing_region', 'customer_address/region', 'default_billing', null, 'left')
->joinAttribute('billing_country_id', 'customer_address/country_id', 'default_billing', null, 'left');
// add 3 new fields as sub queries
$sql ='SELECT MAX(o.created_at)'
. ' FROM ' . Mage::getSingleton('core/resource')->getTableName('sales/order') . ' AS o'
. ' WHERE o.customer_id = e.entity_id ' . ' AND o.status LIKE "complete" ';
$expr = new Zend_Db_Expr('(' . $sql . ')');
$collection->getSelect()->from(null, array('last_order_date'=>$expr));
$sql ='SELECT COUNT(*)'
. ' FROM ' . Mage::getSingleton('core/resource')->getTableName('sales/order') . ' AS o'
. ' WHERE o.customer_id = e.entity_id ' . ' AND o.status LIKE "complete" ';
$expr = new Zend_Db_Expr('(' . $sql . ')');
$collection->getSelect()->from(null, array('orders_count'=>$expr));
$total_pricesql ='SELECT sum(o.base_grand_total)'
. ' FROM ' . Mage::getSingleton('core/resource')->getTableName('sales/order') . ' AS o'
. ' WHERE o.customer_id = e.entity_id ' . ' AND o.status LIKE "complete" ';
$expr = new Zend_Db_Expr('(' . $total_pricesql . ')');
$collection->getSelect()->from(null, array('total_price'=>$expr));
//echo $collection->getSelect(); exit;
$this->setCollection($collection); return parent::_prepareCollection();}
and the second code:
protected function _prepareColumns() {
$this->addColumn('entity_id', array(
'header' => Mage::helper('customer')->__('ID'),
'width' => '50px',
'index' => 'entity_id',
'type' => 'number',
));
$this->addColumn('last_order_date', array(
'header' => Mage::helper('customer')->__('Last Order Date'),
'type' => 'datetime',
'align' => 'center',
'index' => 'last_order_date',
'gmtoffset' => true,
));
$this->addColumn('orders_count', array(
'header' => Mage::helper('customer')->__('Orders Count'),
'index' => 'orders_count',
));
$this->addColumn('total_price', array(
'header' => Mage::helper('sales')->__('Total'),
'index' => 'total_price',
'type' => 'number',
'filter' => false,
));
........
........