CODEWITHSUNDEEP
yii2
Joshi
Joshi

Reputation: 2790

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.

Upvotes: 16

Views: 60639

Answers (5)

Prahlad
Prahlad

Reputation: 796

$invocedAmt = ProjectMilestone::find()
                ->select(['sum(pm_amount) as pm_amount,count(pm_id) as pm_id'])
                ->where(['pm_p_id'=>$Project->p_id,'pm_est_id'=>$estimation->est_id,'pm_status'=>1])
                ->groupBy('pm_est_id')
                ->one();

Upvotes: 0

Dheeraj singh
Dheeraj singh

Reputation: 540

You can directly use yii query concept in Search Model 

$this->find()->from('billing')->where(['column'=>value])->sum('amount');

OR

$this->find()->where(['column'=>value])->sum('amount');

Outside the Model (Billing is model name)

Billing::find()->where(['column'=>value])->sum('amount');

Upvotes: 11

sujoi
sujoi

Reputation: 95

i hope your model name is Billing

inside Billing model use

$this->find()->sum('amount');

in other models

Billing::find()->sum('amount');

Upvotes: 7

Stanimir Stoyanov
Stanimir Stoyanov

Reputation: 1213

Within a model the sum could also be fetched with:

$this->find()->where(...)->sum('column');

Upvotes: 24

Caleb
Caleb

Reputation: 2267

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;

Upvotes: 27

Related Questions