Laravel Eloquent Group By Year with Sum Example

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, 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, '%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...