SUM on a related model field

Let's say I have an Employee model that belongsTo a Department model. I can get department and the number of employees in each department like this:

$depts = Department::withCount('employees')->get();

Now, let's say each employee has a salary and when I get the department list, I need to get the sum of employee salaries for each department. How would I do that?

Vilfago

7 years ago

Level 20

If you want it processed by the db, you should create a custom query using sum : https://laravel.com/docs/5.7/queries#aggregates

Or you can retrieve employee information using with, and then sum the item in collection :https://laravel.com/docs/5.7/collections#method-sum

staudenmeir

7 years ago

Best Answer

Level 24

Use this:

$depts = Department::withCount([
    'employees as salaries' => function($query) {
        $query->select(DB::raw('sum(salary)'));
    }
])->get();

Gaspy

7 years ago

Level 1

@STAUDENMEIR - Yes! This works! Thanks.

Please or to participate in this conversation.