Laravel Group By Date and Sum Example

Hi Dev,

This post will give you example of laravel group by date and sum example. I’m going to show you about laravel group by date d-m-y format. This article will give you simple example of laravel group by date format example. let’s discuss about group by date in laravel eloquent example.

Sometimes, we have created_at column with timestamp and we wanted to get with group by with date, 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 date records. you can easily use this example with laravel 6, laravel 7, laravel 8, laravel 9 and laravel 10 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, '%d-%m-%Y')) as my_date")
                            )
                            ->orderBy('created_at')
                            ->groupBy(DB::raw("DATE_FORMAT(created_at, '%d-%m-%Y')"))
                            ->get();
  
        dd($visitors);
    }
}

Output:

Array
(
    [0] => Array
        (
            [id] => 1
            [total_click] => 4
            [my_date] => 26-10-2020
        )
    [1] => Array
        (
            [id] => 2
            [total_click] => 59
            [my_date] => 23-09-2021
        )
    [2] => Array
        (
            [id] => 6
            [total_click] => 12
            [my_date] => 04-10-2021
        )
    [3] => Array
        (
            [id] => 4
            [total_click] => 55
            [my_date] => 11-10-2021
        )
)

i hope it can help you...