Laravel Eloquent: Sum with Groupby

Laravel Eloquent: sum with groupBy

Document::groupBy('users_editor_id')
   ->selectRaw('sum(no_of_pages) as sum, users_editor_id')
   ->pluck('sum','users_editor_id');

   // originally lists(), which was deprecated in favour of pluck in 5.2
   // and dropped completely in 5.3
   // ->lists('sum','users_editor_id');


// returns array like this:
array(
  users_editor_id => sum,
  ...
)

Or this way (which I wouldn't use, since it won't be actual ORM result):

Document::groupBy('users_editor_id')
   ->selectRaw('*, sum(no_of_pages) as sum')
   ->get();

// returns collection of Document pseudo models with additional sum field

Laravel eloquent sum relationship quantity and groupby

If you want it as an attribute, dont try to query build it like a relation.

you can declare the attribute like this in Product::class

public function getSumCreditsAttribute()
{
    return $this->credits->where('quantity', '>', 1)->sum('quantity');
}

you can also always have it loaded with the appends attribute

protected $appends = ['sum_credits'];

or access it when you have an instance of Product::class

$products = Product::get();
foreach ($products as $product) {
    var_dump($product->sum_credits);
}

Laravel using Sum and Groupby

that a collection group by not an eloquent groupby

if you want to do it with eloquent, gotta:

 $data1 = Borrow::selectRaw('SUM(quantity) as qt, MONTH(created_at) as borrowMonth')
      ->groupBy('borrowMonth')->get();

if you want to do it with the collection groupBy method, you should first do the get, then the groupBy.

As in, though im not sure what you're trying to accomplish with what you do inside the callback..

 $data1 = Borrow::get()->groupBy(function($d) {
 return Carbon::parse($d->created_at)->format('m')->sum('quantity');
 });

how to get total sum of two tables and group responses with eloquent Laravel?

There are a couple of mistakes in your query that I noticed. For instance you don't need to SUM (session_details.spent_points + template_sales.price_points) because this is already performing the addition.

Instead of pointing out all these, let's break your problem down into smaller pieces; when query seems complicated it would be a good idea to break it down for better understanding. There seems to be a couple of tables but I will base my answer on the two tables provided, and that should give you a starting point.

Essentially, what you want is,

Get the sum of spent_points per session_id; so you need to group by session_id and sum(spent_points)

$sumSpentPointsQuery = DB::table('session_details')
                       ->select('session_id', DB::raw('SUM(spent_points) as sum_spent_points'))
                       ->groupBy('session_id');

Get the sum of price_points per session_id; so you need to group by session_id and sum(price_points)

$sumPricePointsQuery = DB::table('template_sales')
                       ->select('session_id', DB::raw('SUM(price_points) as sum_price_points'))
                       ->groupBy('session_id');

Now we need to get the addition of sum_spent_points and sum_price_points. This time our tables would be the results we got from the sub queries. So we can work with Laravel's fromSub and joinSub to get the result we want.

DB::query()
    ->select('ssp.session_id as session_id', DB::raw('sum_spent_points + sum_price_points as suma_total') )
    ->fromSub($sumSpentPointsQuery, 'ssp')
    ->joinSub($sumPricePointsQuery, 'spp', function($join){
         $join->on('ssp.session_id', '=', 'spp.session_id');
    })->get();

This query should produce the sql that represents this:

select ssp.session_id as session_id, (sum_spent_points + sum_price_points) as suma_total 
from 
  (select session_id, sum(spent_points) as sum_spent_points 
  from session_details group by session_id) ssp
inner join 
  (select session_id, sum(price_points) as sum_price_points 
  from template_sales group by session_id) spp 
on ssp.session_id = spp.session_id ;

Hope this kicks you in the right direction.

Mysql sum group by query in laravel eloquent

Using Eloquent Model :

$records = TsData::select(
    'employee',
    'workdate',
    \DB::raw('sum(actualhours) as sumhours')
)
->groupBy('employee', 'workdate')
->get();

or using DB Facade :

$records = \DB::table('ts_data')->select(
    'employee',
    'workdate',
    \DB::raw('sum(actualhours) as sumhours')
)
->groupBy('employee', 'workdate')
->get();

---

Related Topics