Laravel Eloquent Group By Year with Sum Example

By Hardik Savani May 17, 2021 Category : Laravel

This simple article demonstrates of laravel group by year and sum example. i would like to show you laravel group by year with count. We will use laravel eloquent group by year example. you will learn group by year in laravel eloquent example.

Sometimes, we have created_at column with timestamp and we wanted to get with group by with 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 year 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, '%Y')) as my_year")

)

->orderBy('created_at')

->groupBy(DB::raw("DATE_FORMAT(created_at, '%Y')"))

->get();

dd($visitors);

}

}

Output:

Array

(

[0] => Array

(

[id] => 1

[total_click] => 29

[my_year] => 2020

)

[1] => Array

(

[id] => 3

[total_click] => 101

[my_year] => 2021

)

)

i hope it can help you...