A List of All Available Database Aggregation Methods in Laravel Eloquent

• 1. The count() Method
• 2. The max() Method
• 3. The min() Method
• 4. The avg() or average() Method
• 5. The sum() Method
• 6. The value() Method
• 7. Aggregate Methods with Conditions
• 8. Advanced Aggregations: groupBy() and having()
• 9. Raw Aggregate Methods

Laravel's Eloquent ORM provides a rich set of tools for interacting with databases. Among these tools are aggregation methods, which allow developers to easily calculate totals, averages, and other aggregates directly from their models. In this article, we'll explore all of the available aggregation methods provided by Eloquent, diving into their usage and benefits.

Throughout this tutorial we'll try and use similar examples of employee salary queries.

¶1. The count() Method

Easily one of the most frequently used aggregation methods, count() lets you determine the number of records in a table.

$usersCount = User::count();

¶2. The max() Method

Retrieve the highest value from a specific column using the max() method.

$highestSalary = Employee::max('salary');

¶3. The min() Method

Conversely, to find the smallest value in a column, employ the min() method.

$lowestSalary = Employee::min('salary');

¶4. The avg() or average() Method

Compute the average value of a specific column with the avg() or its alias average().

$averageSalary = Employee::avg('salary');

¶5. The sum() Method

When you need the combined total of a column, turn to the sum() method.

$totalEarnings = Order::sum('amount');

¶6. The value() Method

For situations where you expect a single result from your query, the value() method can be used. It retrieves the value of the first result of the query.

$name = User::where('name', 'John')->value('name');

¶7. Aggregate Methods with Conditions

You're not restricted to using these methods on entire tables. Combine them with other query builder methods for more specific aggregations.

$totalEarningsLastMonth = Order::whereMonth('created_at', now()->subMonth()->month)
                               ->sum('amount');

¶8. Advanced Aggregations: groupBy() and having()

For more advanced aggregation needs, use the groupBy() and having() methods.

$monthlyEarnings = Order::selectRaw('MONTH(created_at) as month, sum(amount) as total')
                        ->groupBy('month')
                        ->having('total', '>', 1000)
                        ->get();

¶9. Raw Aggregate Methods

While Eloquent provides a fluent interface for many of its features, sometimes you might need to use raw SQL expressions. Use the DB::raw() method in conjunction with aggregate methods for such cases.

$averageDailyEarnings = Order::select(DB::raw('DAY(created_at) as day, AVG(amount) as average'))
                             ->groupBy('day')
                             ->get();

Laravel's Eloquent ORM provides a multitude of aggregation methods to simplify database interactions and calculations. Leveraging these methods in your Laravel applications enables efficient and expressive data computations, elevating the quality and clarity of your code.