Search code examples
yii2

Yii2 - getting sum of a column

I found this in the guide, but have no idea how to implement the same

yii\db\Query::count(); returns the result of a COUNT query. Other similar methods include sum($q), average($q), max($q), min($q), which support the so-called aggregational data query. $q parameter is mandatory for these methods and can be either the column name or expression.

Say for example I have a table name 'billing' with columns:

name     amount
charge1  110.00
charge2  510.00
Total -  620.00

How I implement using

yii\db\Query::sum('amount');

I have also tried like

$command = Yii::$app->db->createCommand("SELECT sum(amount) FROM billing");

yii\db\Query::sum($command);

but page generates error.

Thanks.

Solution

  • The first part of code you tried appears to be attempting to use Query Builder. In this case, you must create an instance of a query, set the target table, and then compute the sum:

    Via Query Builder (http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html):

    $query = (new \yii\db\Query())->from('billing');
$sum = $query->sum('amount');
echo $sum;

    The second part of code you tried appears to be attempting to use Data Access Objects. In this case, you can write raw SQL to query the database, but must use queryOne(), queryAll(), queryColumn(), or queryScalar() to execute the query. queryScalar() is appropriate for an aggregate query such as this one.

    Via Data Access Objects (http://www.yiiframework.com/doc-2.0/guide-db-dao.html):

    $command = Yii::$app->db->createCommand("SELECT sum(amount) FROM billing");
$sum = $command->queryScalar();
echo $sum;