Laravel Eloquent Group By with Month and Year Example

Hi Dev,

Here, i will show you laravel group query result by month and year. i explained simply step by step laravel group by month and sum. this example will help you laravel group by month and year. this example will help you group by month and year laravel.

Sometimes, we have created_at column with timestamp and we wanted to get with group by with month and year, that way we can use for chart. However, you can do it using mysql DATE_FORMAT() function. we can use DB raw with mysql function and get group by monthly records. you can easily use this example with laravel 6, laravel 7, laravel 8, laravel 9, laravel 10 and laravel 11 version.

Let's see bellow example:

Table:

Laravel Eloquent Query:

<?php
   
namespace App\Http\Controllers;
  
use App\Models\Visitor;
use DB;
  
class DemoController extends Controller
{
    /**
     * Write code on Method
     *
     * @return response()
     */
    public function index()
    {
        $visitors = Visitor::select(
                            "id" ,
                            DB::raw("(sum(click)) as total_click"),
                            DB::raw("(DATE_FORMAT(created_at, '%m-%Y')) as month_year")
                            )
                            ->orderBy('created_at')
                            ->groupBy(DB::raw("DATE_FORMAT(created_at, '%m-%Y')"))
                            ->get();
  
        dd($visitors);
    }
}

Output:

Array
(
    [0] => Array
        (
            [id] => 2
            [total_click] => 25
            [month_year] => 09-2019
        )
    [1] => Array
        (
            [id] => 1
            [total_click] => 4
            [month_year] => 10-2020
        )
    [2] => Array
        (
            [id] => 3
            [total_click] => 34
            [month_year] => 09-2021
        )
    [3] => Array
        (
            [id] => 4
            [total_click] => 35
            [month_year] => 10-2021
        )
    [4] => Array
        (
            [id] => 5
            [total_click] => 32
            [month_year] => 11-2021
        )
)

i hope it can help you...