Group collection SUM by YEAR

show us ur current code?

you can do this in mysql directly by adding a group by clause something liek:

select sum(amount) from table group by Year(created_at)

thanks @shez1983. I went with this:

DB::select('SELECT YEAR(created_at) as year, SUM(amount) as total FROM homestead.projects GROUP BY YEAR(created_at)');

Eloquent, and especially Collections, can make this process quite slick.

1. Select your collection of projects to study, below I use ->all()
2. Group them in whatever classes you like: you want created_at by Year-Month
3. Sum the amounts in those groups

So,

        $monthlyAmounts = Project::all()
            ->groupBy(function ($proj) {
                return $proj->created_at->format('Ym');
            })
            ->map(function ($month) {
                return $month->sum('amount');
            });

Correction,

you did say you wanted yearly, so a simple change to the equivalence classes:

$yearlyAmount = Project::all()
    ->groupBy(function ($proj) {
        return $proj->created_at->format('Y');
    })
    ->map(function ($year) {
        return $year->sum('amount');
    });

it is better (For performance reasons) to do it in SQL then to do it in the PHP afterwards..

@shez1983

It is "faster" in some regards, but much harder to debug the data being collected. Unless I am dealing with a large dataset (1000+ records) I prefer to use @burlresearch 's method.

This comes down to the amount of data being processed. The direct query is a very viable solution, and in the simplistics of this query I would not see anything wrong with the query. If it was a more complex query that has many points of error, it is sometimes better to give up a bit of speed and instead go with a more debuggable solution.

I understood the question to be about Laravel Collections:

I would like to see how using collections can simplify this.

Yes, wanted to see how collections could make this happen. Isn't the collection method still using SQL anyway on the backend?

Thanks both of your for providing examples!

nop it isnt..

@cmdobueno in most cases it will be faster for SQL to do the sort than for you to fetch all the records and then sort in your app.. with 100 records you may not see a huge difference but there will always be a difference...

@burlresearch sure the OP said collection but usually when people answer they also try to give 'better' (or alternative) solution