# Sum of associated Fields

**URL:** <https://discourse.cakephp.org/t/sum-of-associated-fields/9915>\
**Category:** Need Help\
**Created:** [November 19, 2021, 10:02pm UTC](https://discourse.cakephp.org/t/sum-of-associated-fields/9915 "2021-11-19T22:02:42Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![gersin](https://avatars.discourse-cdn.com/v4/letter/g/f08c70/32.png) [@gersin](https://discourse.cakephp.org/u/gersin)\
**Post date:** [November 19, 2021, 10:02pm UTC](https://discourse.cakephp.org/t/sum-of-associated-fields/9915/1 "2021-11-19T22:02:42Z")

</div>

I have a problem with 2 Tables, i need a sum of associated table

**Table 1 projects:**

| id | title |
| --- | --- |
| 1 | Project A |
| 2 | Project B |

  

**Table 2 jobs:**

| id | project\_id | hours |
| --- | --- | --- |
| 1 | 1 | 3 |
| 2 | 1 | 2 |
| 3 | 1 | 1 |
| 4 | 2 | 4 |

Association in ProjectsTable:  
$this-\>hasMany(‘Jobs’);

**Expected output:**  
Project A : 6 hours  
Project B : 4 hours

Can anyone help me? I tried a lot but it wont work.

---

<div class="post-metadata">

**Author:** ![Zuluru](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/zuluru/32/1230_2.png) [@Zuluru](https://discourse.cakephp.org/u/Zuluru)\
**Post date:** [November 19, 2021, 10:39pm UTC](https://discourse.cakephp.org/t/sum-of-associated-fields/9915/2 "2021-11-19T22:39:41Z")

</div>

Please share an example or two of what you tried, and how they failed. It’s often easier to fix something that’s close than to create it from scratch for you.

---

<div class="post-metadata">

**Author:** ![gersin](https://avatars.discourse-cdn.com/v4/letter/g/f08c70/32.png) [@gersin](https://discourse.cakephp.org/u/gersin)\
**Post date:** [November 19, 2021, 11:15pm UTC](https://discourse.cakephp.org/t/sum-of-associated-fields/9915/3 "2021-11-19T23:15:32Z")

</div>

I tried something like this in many variations…

$qry = $this-\>find();  
$qry-\>select([  
‘title’,  
‘hours’ =\> $query-\>func()-\>SUM(‘Jobs.hours’)  
])  
-\>group(‘Jobs.project\_id’);

the result after $qry-\>all(), toArray, toList ist often Jobs.hours does not exist. i also tried to add contain.

---

<div class="post-metadata">

**Author:** ![brent](https://avatars.discourse-cdn.com/v4/letter/b/ec9cab/32.png) [@brent](https://discourse.cakephp.org/u/brent)\
**Post date:** [November 21, 2021, 11:54pm UTC](https://discourse.cakephp.org/t/sum-of-associated-fields/9915/4 "2021-11-21T23:54:25Z")

</div>

Is this what you’re looking for?

### Query

```auto
$queryJobs = TableRegistry::getTableLocator()->get('Jobs')->find();
$jobsByProject = $queryJobs
	->select(
		[
			'title' => 'Projects.title',
			'hours' => $queryJobs->func()->sum('Jobs.hours'),
		]
	)
	->innerJoinWith('Projects')
	->group('Jobs.project_id')
	->toArray();

```

#### Output

`\Cake\Log\Log::debug(json_encode($jobsByProject));`

```auto
[
  {
    "title": "Project A",
    "hours": 6
  },
  {
    "title": "Project B",
    "hours": 4
  }
]

```

---

<div class="post-metadata">

**Author:** ![gersin](https://avatars.discourse-cdn.com/v4/letter/g/f08c70/32.png) [@gersin](https://discourse.cakephp.org/u/gersin)\
**Post date:** [November 22, 2021, 8:16am UTC](https://discourse.cakephp.org/t/sum-of-associated-fields/9915/5 "2021-11-22T08:16:34Z")

</div>

Thanks Brent. Thats exactly what i am looking for. You made my day!