Sum and group by month using Laravel Eloquent

I am using Laravel 5.6 and postgres 11.

I log changes in quantity of different equipment types which look something like this:

{location_id: 1, equipment_type_id: 1, qty: 5, total: 5, date: 'YYYY-MM-DD'},
{location_id: 1, equipment_type_id: 1, qty: 5, total: 10, date: 'YYYY-MM-DD'},
{location_id: 1, equipment_type_id: 2, qty: 5, total: 5, date: 'YYYY-MM-DD'},
{location_id: 1, equipment_type_id: 1, qty: 5, total: 15, date: 'YYYY-MM-DD'} etc

I am wanting to be able to get the sum of the total per type grouped by month BUT I only want to get the most recent total for a type and location within the same month. For example if location 1 has 3 entries for type 1, I want to sum the last entry for the month.

Returned data to look something like this:

{type: 1, data: [{month: Jan, total: 15}]},
{type: 2, data: [{month: Jan, total: 10},{month: Feb, total: 15}]}

I had a quick go but this type of query is well over my head:

$singles = EquipmentLog::where('equipment_type_id', '=', 3)
            ->select(
                DB::raw('sum(total) as total'), 
                DB::raw("DATE_TRUNC('month',logged_at) as month")
            )
            ->groupBy('month')
            ->get();

        $totals = [
            ['name' => 'Hives - Single', 'data' => $singles],
        ];

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