GroupBy Laravel Posts by Year and Month

groupBy.php

<?php

use App\Post;

$posts = Post::query()
    ->selectRaw(
        'YEAR(`published_at`) as `year`, MONTH(`published_at`) as `month`, MONTHNAME(`published_at`) as `month_name`, COUNT(id) as `posts_count`'
    )
    ->groupBy('year')
    ->groupBy('month')
    ->groupBy('month_name')
    ->orderBy('year', 'asc')
    ->orderBy('month', 'asc')
    ->get();

$totalPosts = $posts->sum('posts_count');
$groupedPosts = $posts->groupBy('year');

// OUTPUT
// $totalPosts -> 1000
// $groupedPosts -> Collection:: [
//     2018 -> Collection:: [
//         Post:: [
//             'month_name' -> 'January',
//             'posts_count' -> 25,
//         ]
//     ]
// ]