MySQL: How can fetch SUM() of all fields in one Query?

Answer 1

SELECT      SUM(column1) + SUM(column2) + SUM(columnN) 
FROM        mytable 
GROUP BY    year

Answer 2

Using the Zend Framework's Zend_Db_Select, your query might look like

$db = Zend_Db::factory( ...options... );

$select = $db->select()
    ->from('mytable', array('sum1' => 'SUM(`col1`)', 'sum2' => 'SUM(col2)')
    ->group('year');

$stmt = $select->query();
$result = $stmt->fetchAll();

Refer to the Zend_Db_Select documentation in the ZF manual for more.

EDIT: My bad, I think I misunderstood your question. The query above will return each colum summed, but not the sum of all of the columns. Rewriting Maxem's query so that you can use it with a Zend Framework DB adapter, it might look like

$sql = '<insert Maxem's query here>';
$result = $db->fetchAll($sql);

You might choose to use fetchCol() to retrieve the single result.

Answer 3

It sounds like you don't want to explicitly enumerate the columnn and that you want to sum all the columns (probably excluding the year column) over all the rows, with grouping by year.

Note that the method Zend_Db_Table::info(Zend_Db_Table_Abstract::COLS) will return an array containing the columns names for the underlying table. You could build your query using that array, something like the following:

Zend_Db_Table::setDefaultAdapter($db);
$table = new Zend_Db_Table('mytable');
$fields = $table->info(Zend_Db_Table_Abstract::COLS);
unset($fields['year']);
$select = $table->select();
$cols = array();
foreach ($fields as $field){
   $cols[] = sprintf('SUM(%s)', $field);
}
$select->cols(implode(' + ', $cols));
$select->group('year');

I have not tested the specific syntax, but the core of the idea is the call to info() to get the fields dynamically.