Laravel groupBy month and sum the total

Question

Data Structure

[
{
  "id": "1",
  "date": "2019-06-22",
  "total": "1200" // THIS TOTAL IS A MUTATOR
},
{
  "id": "2",
  "date": "2019-06-24",
  "total": "2200" // THIS TOTAL IS A MUTATOR
},
{
  "id": "3",
  "date": "2019-07-20",
  "total": "2300" // THIS TOTAL IS A MUTATOR
},
]

What I am trying to do:

I want to group these data by MONTH and return the sum

Expected result:

2019-06 total is 1200 AND 2019-07 total is 4500

What I have tried:

$customer_branch = ExportInvoice::where('customer_branch_id', $customer_branch_id)
            -> approved()
            -> whereYear('date', $year)
            -> orderBy('date', 'ASC')
            -> get();

And I have tried to loop throw $customer_branch and set data to array but I did not get the expected result

Answer 1

1.Open config/database.php

2.Find strict key inside mysql connection settings

3.Set the value to false

4.Then Run php artisan config:cache

Now Try this Query

ExportInvoice::select('id',\DB::raw('SUM(total) as total,DATE_FORMAT(date,"%Y-%m") as monthDate'))
->where('customer_branch_id', $customer_branch_id)
->approved()
->groupBy(\DB::raw('DATE_FORMAT(date,"%Y-%m")'))
->orderBy('date')
->get();

Answer 2

A simble DB:raw within a groupBy should do the trick.

$customer_branch = ExportInvoice::where('customer_branch_id', $customer_branch_id)
   ->approved()
   ->whereYear('date', $year)
   ->groupBy(DB::raw(DATE_FORMAT(date,'%Y-%m'))
   ->get();