Aggregate functions in Magento ORM

Question

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

Consider there is a table "test" with following values

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

and if i mapped this table in my module,so that i can get collection data like

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

how do i get result equivalent to below query with COLLECTION?

select sum(qty) from test;

Answer 1

If your mymodule/customcart collection is EAV based 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 the 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 doesn't handle grouped queries well, you might have to provide your own getSelectCountSql function which returns an accurate count.