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

**URL:** <https://discourse.cakephp.org/t/how-to-sum-value-according-to-other-column-value-in-cakephp/1314>\
**Category:** Need Help\
**Created:** [October 11, 2016, 3:38pm UTC](https://discourse.cakephp.org/t/how-to-sum-value-according-to-other-column-value-in-cakephp/1314 "2016-10-11T15:38:03Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![sahar](https://avatars.discourse-cdn.com/v4/letter/s/ea5d25/32.png) [@sahar](https://discourse.cakephp.org/u/sahar)\
**Post date:** [October 11, 2016, 3:38pm UTC](https://discourse.cakephp.org/t/how-to-sum-value-according-to-other-column-value-in-cakephp/1314/1 "2016-10-11T15:38:03Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![sahar](https://avatars.discourse-cdn.com/v4/letter/s/ea5d25/32.png) [@sahar](https://discourse.cakephp.org/u/sahar)\
**Post date:** [October 14, 2016, 7:01pm UTC](https://discourse.cakephp.org/t/how-to-sum-value-according-to-other-column-value-in-cakephp/1314/2 "2016-10-14T19:01:50Z")

</div>

I’m using Cakephp Version 2.6.1

Please some one check and guide me.

---

<div class="post-metadata">

**Author:** ![dakota](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/dakota/32/1421_2.png) [@dakota](https://discourse.cakephp.org/u/dakota)\
**Post date:** [November 1, 2016, 6:07am UTC](https://discourse.cakephp.org/t/how-to-sum-value-according-to-other-column-value-in-cakephp/1314/3 "2016-11-01T06:07:54Z")

</div>

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](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](http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#find-list)

Something like

```php
$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.