I have a database named maintenance
which has a field named cost
. I now want to get the sum of costs by month and year
and sort them in descending order by month and year
. Can anyone help me?
This is what I tried
$MaintenanceStats = Maintenance::oldest() ->get() ->groupBy(function($val) { return Carbon::parse($val->from)->format('F'); }) ->take(7);
I got the collection object below and it works fine. But how can I group them by month and year and then sort them in descending order instead of just grouping by month? Also, I only need the total monthly expenses; I don't need all the maintenance records.
Illuminate\Database\Eloquent\Collection {#1517 ▼ // app\Http\Controllers\GeneralController.php:40 #items: array:5 [▼ "September" => Illuminate\Database\Eloquent\Collection {#1452 ▶} "July" => Illuminate\Database\Eloquent\Collection {#1530 ▶} "January" => Illuminate\Database\Eloquent\Collection {#1519 ▶} "November" => Illuminate\Database\Eloquent\Collection {#1520 ▶} "December" => Illuminate\Database\Eloquent\Collection {#1521 ▶} ] #escapeWhenCastingToString: false }
P粉5614384072024-03-29 11:08:04
The best way is to use a database query to accomplish this, but you will also need a raw query:
$maintenanceStats = Maintenance::selectRaw("year(`from`) AS year, month(`from`) AS month, sum(cost) AS cost_total") ->groupByRaw("year(`from`)") ->groupByRaw("month(`from`)") ->orderBy('year') ->orderBy('month') ->get();