Query with subqueries with group by - Help ;)

Hello,

I'm trying to write a query with multiple subqueries with group by. Difficult to explai, so here is an example.

The models tables.

societies : id, name
employees : id, name, society_id
trainings : id, name
sources : id, name, subsidie (bool), plan (bool)

The pivot tables.

training_employee : training_d, employee_id
fundings : amount, training_id, source_id

An example of rows of the result of my query. McGyver and Magnum are both registered on the same training.

ROW 1
- My society name (from societies)
- How to sell cars (from trainings, depends on society_id)
- McGyver (from salaries, depends on the pivot table)
- Subsidies $ 400 (sum of fundings with subsidie true, depends on the training_id)
- Plans $ 800 (sum of fundings with plan true, depends on the training_id)

ROW 2
- My society name (from societies)
- How to sell cars (from trainings, depends on society_id)
- Magnum (from salaries, depends on the pivot table)
- Subsidies $ 400 (sum of fundings with subsidie true, depends on the training_id)
- Plans $ 800 (sum of fundings with plan true, depends on the training_id)

The amounts are the same because they depend only on the training.

So one row per employee.

Here is my initial query, but I don't know how to implement the subqueries to calculate the sum of the funding according to the subsidies / plans bool fields.

$trainings = Training::
	withCount('employees')
    ->join('training_employee', 'training_employee.training_id', '=', 'trainings.id')
    ->join('employees', 'employees.id', '=', 'training_employee.employee_id')
    ->where('trainings.society_id', $society_id)
	->get();

Please can someone help me ;).

Thanks a lot.

Vincent