Aggregate Features in Magento ORM

Question

How to use aggregate functions like sum (), count () in Magento ORM.

Consider the table "test" with the following values

------------------------------------- productid | qty ------------------------------------- 1 | 2 ------------------------------------- 2 | 3 ------------------------------------- 3 | 5 ------------------------------------- 

and if I matched this table in my module so that I can get collection data, for example

 $_collection = Mage::getModel('mymodule/customcart')->getCollection();
$_collection = Mage::getModel('mymodule/customcart')->getCollection(); 

How to get result equivalent to below query using COLLECTION?

 select sum(qty) from test;
select sum(qty) from test; 

Answer 1

If your mymodule/customcart based on EAV, you can use addExpressionAttributeToSelect

 $_collection = Mage::getModel('mymodule/customcart')->getCollection(); $_collection->addExpressionAttributeToSelect('qty', 'SUM({{item_qty}})', 'item_qty') ->groupByAttribute('productid'); 

Otherwise, you will have to work with SQL more directly:

 $_collection = Mage::getModel('mymodule/customcart')->getCollection(); $_collection->getSelect() ->columns('SUM(item_qty) AS qty') ->group('productid'); 

Be careful when using aggregated collections with grids or pagers. They rely on getSelectCountSql , which does not handle grouped queries well, you may have to provide your own getSelectCountSql function, which returns the exact score.