MySQL: How to get SUM () from all fields in one query?
I just need something like this:
select SUM(*) from `mytable` group by `year`
any suggestion?
(I'm using Zend Framework, if you have a suggestion to use ZF rather than a pure query, that would be great!)
Update: I have a ton of columns in my table and I don't want to write their name one at a time.
No idea?
a source to share
Using Zend Framework Zend_Db_Select, your request 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();
See the Zend_Db_Select documentation in the ZF manual for details .
EDIT: My bad one, I think I misunderstood your question. The query above will return each total, but not the total of all columns. When rewriting the Maxem query so you can use it with the Zend Framework DB adapter it might look like
$sql = '<insert Maxem query here>';
$result = $db->fetchAll($sql);
You can use fetchCol () to get a single result.
a source to share
It looks like you don't want to explicitly list columnn and that you want to sum all columns (possibly excluding the column year
) across all rows, grouped by year.
Note that the method Zend_Db_Table::info(Zend_Db_Table_Abstract::COLS)
will return an array containing the column names for the base table. You can create your request using this array, for example:
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 haven't tested the specific syntax, but the core idea is a call info()
to get fields dynamically.
a source to share
Executed in ZF instead of a pure query and you don't need to write the column names one by one. (I am assuming you are extending Zend_Db_Table_Abstract)
If you are asking how to write
select SUM(*) from `mytable` group by `year`
Here's how it's done:
public function sumOfAllFields(){
return $this->fetchAll( $this->select()->from('mytable','SUM(*)')->group('year') )->toArray();
}
a source to share
Or not use Zend ...
function mysql_cols($table){
$sql="SHOW COLUMNS FROM `".$table."`";
$res=mysql_query($sql);
$cols=array();
while($row=mysql_fetch_assoc($res))$cols[]=$row['Field'];
return $cols;
}
$cols=mysql_cols("mytable");
$select_sql=array();
foreach($cols as $col){
$select_sql[]="SUM(`".$col."`)";
}
$select_sql=implode('+',$select_sql);
$sql="select (".$select_sql.") from `mytable` group by `year`";
a source to share