Zend DB Table Abstract

I've hit a bit of a brick wall in converting an SQL Query into a suitable ORM Query

The query is pretty complex even in SQL Terms, But I am looking at ways of collating this into an ORM Query, as I'm converting a lot of functions I've produced in Flex into Zend Framework Models for future proofing and API Purposes.

The current flat SQL is as follows :

SELECT subcategory_id, 
SUM(total) / (1+(YEAR(2011)*12+MONTH(1))-(YEAR(2011)*12+MONTH(12)))      
AS monthly_average FROM aggr_subcategory WHERE user_id=1 AND month BETWEEN 01 AND 05 
         GROUP BY user_id, subcategory_开发者_运维知识库id

The main issues I am having are the SUM, I can manage the GROUP and WHERE clauses in ORM, but cannot get the sum in the DB Table Abstract.

Any help or points in the right direction much appreciated.

What about this:

    $db = Zend_Db_Table::getDefaultAdapter();

    $select = $db->select();

    $dbExpr = new Zend_Db_Expr("SUM(total) / (1+(YEAR(2011)*12+MONTH(1))-(YEAR(2011)*12+MONTH(12))) AS monthly_average");

    $select->from(
               'aggr_subcategory', 
               array('subcategory_id', $dbExpr)               
            )->where('user_id = 1')
             ->where('month BETWEEN 01 AND 05')
             ->group(array('user_id', 'subcategory_id'));


    echo $select->assemble();
    exit;

Results in:

SELECT `aggr_subcategory`.`subcategory_id`,
SUM(total) / (1+(YEAR(2011)*12+MONTH(1))-(YEAR(2011)*12+MONTH(12))) AS monthly_average 
FROM `aggr_subcategory` 
WHERE (user_id = 1) AND (month BETWEEN 01 AND 05) 
GROUP BY `user_id`, `subcategory_id

`

Although ORMs are great tools, they cannot (and shouldn't try to) entirely replace native sql. You can simply execute your sql by:

$db->query($sql);