Laravel Group By with Month and Year Example

Hello Artisan,

This article will give you an example of laravel group by year month. This tutorial will give you a simple example of laravel group by month from date field. In this article, we will implement a laravel group by month and sum. you can understand a concept of laravel group by month example.

Sometimes, we have created_at column with timestamp and we wanted to get with group by with month, that way we can use for chart. However, you can do it using MySQL MONTH() and YEAR() function. we can use DB raw with mysql function and get group by monthly records.

So, you can see bellow query and use it.

Example:

<?php
     
namespace App\Http\Controllers;
    
use Illuminate\Http\Request;
use DB;
    
class ClickController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index(Request $request)
    {
        $data = DB::table("clicks")
                       ->select("id" 
                                ,DB::raw("(SUM(*)) as total_clicks")
                                ,DB::raw('YEAR(created_at) year, MONTH(created_at) month'))
                            ->orderBy('created_at')
                            ->groupBy('year','month')
                            ->get();
  
        dd($data);
    }
}

I hope it can help you...