Laravel Eloquent, group by month/year

i’m trying to group my data by month and year.

$data ->select(DB::raw('count(id) as `data`'),DB::raw('YEAR(created_at) year, MONTH(created_at) month'))
           ->groupby('year','month')
           ->get();

The output is :

{
"data": 19215,
"year": 2016,
"month": 10
},

if i group only by month, i don’t know from which year belong this month, my expected output is :

{
"clicks": 19215,
"month": 11-2016,
},
{
"clicks": 11215,
"month": 12-2016,
},

i want to do it in sql, not in php.

Answer

You can try as:

->select(DB::raw('count(id) as `data`'), DB::raw("DATE_FORMAT(created_at, '%m-%Y') new_date"),  DB::raw('YEAR(created_at) year, MONTH(created_at) month'))
->groupby('year','month')
->get();