[FIXED] How to get sum of a column in mysql in yii

Issue

I have the following code and I want the sum of the column amount. I am using relation function for join of two tables bd_master and bd_details. No errors are showing but the only selected sum amount field is not there.

*$subQuery = BdMaster::model()->find(array('select'=>'id','condition'=>'is_default=1'));
    $bDetails = BdMaster::model()->findAll(array(
                    'condition' => "t.id in ($subQuery->id) and bd_details.period <=LAST_DAY('2016-01-01') and bd_details.period >LAST_DAY('2016-01-01' - INTERVAL 1 MONTH)",
                    'with'=>array('bd_details'),
                    'select'=> 'SUM(bd_details.amount) as sm',
                    ));* 

Solution

The thing is, that the function findAll() return you the model class. If you want to get the sum from the Database, you should build the query (without Model).

The difference is that Query Builder has another syntax. Use link above for the exact documentation. The query you want to build will look probably like this:

$subQuery = BdMaster::model()->find(array('select'=>'id','condition'=>'is_default=1'));
$sum = Yii::app()->db->createCommand()
    ->select('SUM(bd_details.amount) as sm')
    ->from('bdMaster t')
    ->join('bd_details', 't.id=bd_details.bdMaster_id') // here the correct join condition
    ->where("t.id in ($subQuery->id) and bd_details.period <=LAST_DAY('2016-01-01') and bd_details.period >LAST_DAY('2016-01-01' - INTERVAL 1 MONTH)")
    ->queryScalar();

You should adjust the join condition, then the query should work correctly.

queryScalar() will return you the number, so you will get the sum of the column.

Another possibility is to build query with SQL (I actually prefer it more than building with query builder). If you already has your SQL that deliver you the correct result, just put it in variable like this:

$sql = "your SQL here";
$sum = Yii::app()->db->createCommand($sql)->queryScalar();

Then you will also get the correct result. I hope it will help you with your problem. If you still have any questions - feel free to ask.

Answered By - Bfcm