'Summing over multiple fields in Laravel

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?

phplaravel


Solution 1:[1]

Building on simones answer. You could do this by essentially running two queries.

$query = DB::table('events')->join('attendees', 'events.id', '=', 'attendees.event_id');

$raised = $query->sum( 'total_raised' );

$hours = $query->sum( 'total_hours' );

It depends on the situation. If it were on the admin/CMS side of things I'd be lean towards this solution. If it is on the front end it should be done in a single query which will be faster. Depending on the content it may or may not be a significant difference.

$result = DB::table('events')->join('attendees', 'events.id', '=', 'attendees.event_id')
    ->get( array(
        DB::raw( 'SUM(attendees.total_raised) AS raised' ),
        DB::raw( 'SUM(attendees.total_hours) AS hours' ),
    ));

Solution 2:[2]

I am doing the same thing in my project, Here is the solution which I found. I am using Laravel 5.2 Eloquent here is the Eloquent statement.

This statement which I use in my project, Please made change according to your need.

$result = self::select("*", DB::raw('SUM(auction_amount) as total_auction_amount') , DB::raw('SUM(commission_amount) as total_commission_amount'), 
            DB::raw('SUM(deposit_amount) as total_deposit_amount'))
            ->groupBy('cp_user_id')
            ->get()
            ->toArray();

Same way you can use for your query like

$result = self::select("*", DB::raw('SUM(auction_amount) as total_auction_amount') , DB::raw('SUM(Attendees.total_raised) as total_raised'), 
            DB::raw('SUM(Attendees.total_hours) as total_hours'))
            ->with('Attendees')
            ->groupBy('id')
            ->get()
            ->toArray();

Solution 3:[3]

I am writing this answer to help those who are in search to sum multiple fields in a single table.

If you want to sum multiple fields inside a single table so there would be no need to "join" you can simply do it likewise, assuming the table like this. enter image description here

In your controller do this:

$billInfo= Bills::where('reports_id',2)->get( array(
        DB::raw('SUM(Price) as total_price'),
        DB::raw('SUM(balance) as total_balance'),
        DB::raw('SUM(paid) as total_paid'),
      ));

this will result the below data:

[{"total_price":17500,"total_balance":17500,"total_paid":null}]

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Collin James
Solution 2 Nikunj K.
Solution 3 Sabaoon Bedar

Related Questions