Summing over multiple fields in Laravel

Question

I'm wondering if it is possible to take the sum of multiple fields in one query using the fluent query builder.

I currently have two tables: events and attendees. Attendees belong to events and have two fields: total_raised and total_hours. What I want to do is select all events and the total amount raised/total number of hours spent on that event. Now, if I were just using SQL I would do something to the effect of:

 SELECT Event.id, sum(Attendees.total_raised), sum(Attendees.total_hours)
 FROM Events JOIN Attendees ON Events.id = Attendees.event_id 
 GROUP BY Event.id

However, I can't seem to find a way to take multiple sums at once using the fluent query builder. Is there any way to do what I'm trying to do using fluent, or should I just make it a raw SQL query?

Answer 1

You can use sum() i.e.:

$q = DB::table('events')
       ->join('attendees', 'events.id', '=', 'attendees.event_id')
       ->sum('total_raised')
       ->sum('total_hours');

If that doesn't work you can try:

...

->get(
  array(
    'events.id',
    DB::raw('SUM(attendees.total_raised)'),
    DB::raw('SUM(attendees.total_hours)')
  )
);