Multiple groupBy with date in Laravel?

How can I achieve this output (image below).

This is my Types Table.

and this is my Accounts Table.

This is my Type Model.

public function accounts()
{
    return $this->hasMany('App\Accounts');
}

This is my Account Model.

public function type()
{
    return $this->belongsTo('App\Type', 'type_id');
}

What I want to do is, I want to group the accounts table by types and by month, and then I want to add all amount on each month, and display to the table using DataTables.

I tried this code, but it's not giving the expected output.

$query = Accounts::with('type')->whereYear('created_at', $selectedYear)->select('type_id',
    DB::raw('type_id as type_id'), 
    DB::raw('sum(amount) as total_amount'),
    DB::raw("DATE_FORMAT(created_at,'%m') as months")
)->groupBy('months')->get();

I'm still stuck on this part,

Thanks in advance

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/3ifUaGO
via IFTTT