How to Sum value according to other column value in Cakephp?

Need Help
1

Hi, I’m working on a Cakephp project. I want to find sum value from a table according to id.

I’m cakephp learner and working on a project. I have a problem. I did google but unable to find the right solution. I hope, I can find the solution here.

Here is My Code.

function getCompanySales(){
$model=ClassRegistry::init(‘Customer’);
$modelcompany=ClassRegistry::init(‘Company’);
$from = date(“Y-m”)."-01 00:00:00";
$days_in_month = cal_days_in_month(CAL_GREGORIAN, date(‘m’), date(‘Y’));
$to = date(“Y-m”)."-".$days_in_month." 24:00:00";
$companyname=$modelcompany->find(‘all’);
$companiessales=$model->find(‘all’,array(
‘conditions’ => array(
“AND” =>array(
“Customer.created >=” => $from,
“Customer.created <=” => $to
)
),
‘recursive’=>-1
)
);
return $companiessales;
} // Get Company list
and I’m getting result like

Array ( 
[0] => Array ( [Customer] => Array ( [gateway] => 10 [amount] => 349 ) ) 
[1] => Array ( [Customer] => Array ( [gateway] => 7 [amount] => 150 ) ) 
[2] => Array ( [Customer] => Array ( [gateway] => 13 [amount] => 349 ) ) 
[3] => Array ( [Customer] => Array ( [gateway] => 10 [amount] => 350 ) ) 
[4] => Array ( [Customer] => Array ( [gateway] => 7 [amount] => 100 ) ) 
[5] => Array ( [Customer] => Array ( [gateway] => 9 [amount] => 299 ) ) 
[6] => Array ( [Customer] => Array ( [gateway] => 7 [amount] => 249 ) ) 
[7] => Array ( [Customer] => Array ( [gateway] => 10 [amount] => 249 ) ) 
[8] => Array ( [Customer] => Array ( [gateway] => 7 [amount] => 299 ) ) 
[9] => Array ( [Customer] => Array ( [gateway] => 12 [amount] => 199 ) ) 
[10] => Array ( [Customer] => Array ( [gateway] => 7 [amount] => 150 ) )      )

But I want to Sum the Value with same gateway like

array(
[7] => 948 //sum of gateway 7
[9] => 299 //sum of gateway 9
[10] => 948 //sum of gateway 10
[12] => 199 //sum of gateway 12
[13] => 349 //sum of gateway 13
)

In another query =>

array(
[7] => company 1
[9] => company 2 
[10] => company 3
[12] => company 4
[13] => company 5

)

I want a final result as

array(
[company1] => 948
[company1] => 299
[company1] => 948
[company1] => 199
[company1] => 349
)

Thanks for all your help. I’m sure I’ll get the easy steps to resolve the issue.

2

I’m using Cakephp Version 2.6.1

Please some one check and guide me.

3

You should be able to do this in a single query using associations, containable, find('list') and group.

Containable: http://book.cakephp.org/2.0/en/core-libraries/behaviors/containable.html
List: http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#find-list

Something like

$model->virtualFields = array('amount_sum' => 'sum(amount)');
$companiessales=$model->find('list',array(
        'fields' => array('Company.name', 'amount_sum'),
        'conditions' => array(
            "Customer.created >=" => $from,
            "Customer.created <=" => $to
        ),
        'contain' => ['Company'],
        'group' => ['Customer.gateway']
    )
);

Something like that should work provided associations are correctly configured.

1 Like