Laravel Eloquent Group By with Month and Year Example

By Hardik Savani May 15, 2021 Category : Laravel

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 and laravel 8 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...