Group by month and years eloquent laravel

Question

I want count passengers and the result group by month and year maybe like this (just an example), so what's query on laravel to call like this ?

{

2019 => [

January=>0,

Februari=>0,

March=>0,

April=>0,

May=>0,

June=>0,

July=>0,

August=>0,

September=>0,

October=>0,

November=>0,

December=>2,

],

2020 => [

January=>0,

Februari=>0,

March=>4,

April=>2,

May=>1,

June=>0,

July=>0,

August=>0,

September=>0,

October=>0,

November=>0,

December=>0,

],

}

but if that's not possible, I want to know the query to result group by month just in one year on laravel thank you

OMR · Answer 1 · 2020-05-08T16:01:56.563

0

    $values=Passenger::select(DB::raw('count(name)',
DB::raw("DATE_FORMAT(date, '%m-%Y') new_date",
DB::raw('YEAR(date) year, MONTH(date) month'))
->groupby('year','month')->get();

please make sure that you table name is 'passengers' and your model name is Passenger

see : https://stackoverflow.com/a/3366929/10573560

https://stackoverflow.com/a/40529488/10573560

edited May 08 '20 at 16:01

answered May 08 '20 at 14:51

OMR

11,736
5
20
35

it says error like this Illuminate\Database\QueryException: SQLSTATE[42883]: Undefined function: 7 ERROR: function year(date) does not exist.. I'm working with laravel – Muhammad Zainul Mutaqin May 08 '20 at 15:48
still error : Illuminate\Database\QueryException: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "year" LINE 1: ..., DATE_FORMAT(date, '%m-%Y') new_date, YEAR(date) year, MONT... – Muhammad Zainul Mutaqin May 08 '20 at 21:30

Group by month and years eloquent laravel

1 Answers1