Laravel group record by months and sum price

Question

Hello i am trying to group record by months and sum price from orders

I tried something like this

       $order = Order::select(DB::raw('sum(price) as sums'))->groupBy(function($date) {
    return Carbon::parse($date->created_at)->format('Y-m');

});

but no luck, I wanna get collection which I could later implement in data-tables?

Answer 1

Please try with this:

  $orders = Order::select(
            DB::raw('sum(price) as sums'), 
            DB::raw("DATE_FORMAT(created_at,'%M %Y') as months")
  )
  ->groupBy('months')
  ->get();

Answer 2

As an addition to @Anowar Hossain answer, you may use this solution to further get the index of other months regardless of whether they have values in them or not.

$orders = Order::select(
            DB::raw('sum(price) as sums'), 
            DB::raw("DATE_FORMAT(created_at,'%M %Y') as months"),
            DB::raw("DATE_FORMAT(created_at,'%m') as monthKey")
  )
  ->groupBy('months', 'monthKey')
  ->orderBy('created_at', 'ASC')
  ->get();

If you're looking to provide data for the year only, you can consider doing this instead:

$orders = Order::select(
            DB::raw('sum(price) as sums'), 
            DB::raw("DATE_FORMAT(created_at,'%m') as monthKey")
  )
  ->whereYear('created_at', date('Y'))
  ->groupBy('monthKey')
  ->orderBy('created_at', 'ASC')
  ->get();

Then create an array with 12 zero values like this:

$data = [0,0,0,0,0,0,0,0,0,0,0,0];

foreach($orders as $order){
    $data[$order->monthKey-1] = $order->sums;
}

return $data; // [0,10000,5000,7000,9000,0,0,0,0,15000,0,0]

Hope this helps someone out there!

Answer 3

Here’s a little more coherent way to do @okafor-t-kosiso’s answer:

$payments = Order::select( 
        \DB::raw('SUM(price) as subtotal'), 
        \DB::raw("EXTRACT(YEAR FROM `created_at`) as year"),
        \DB::raw("EXTRACT(MONTH FROM `created_at`) as month")
      )->whereBetween('created_at', [$start, $end])
      ->groupBy('month', 'year');

Results in something like:

|----------------------|
|subtotal |year |month |
|----------------------|
|234.32   |2022 |2     |
|654.02   |2022 |3     |
|35.00    |null |null  |
|----------------------|

Tested, and this will also actually aggregate on null date columns.