Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Yii2 - getting sum of a column

Tags:

yii2

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.

like image 393
Joshi Avatar asked Nov 10 '14 20:11

Joshi


2 Answers

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;
like image 165
Caleb Avatar answered Sep 20 '22 14:09

Caleb


Within a model the sum could also be fetched with:

$this->find()->where(...)->sum('column');
like image 30
Stanimir Stoyanov Avatar answered Sep 22 '22 14:09

Stanimir Stoyanov