Eloquent Aggregates: Count, Sum, Max, Min, Average

Introduction

Laravel’s Eloquent ORM provides a beautiful, simple ActiveRecord implementation for working with your database. One of the most powerful features of Eloquent is the ability to query your database using various aggregate functions. In this tutorial, we’re going to take a comprehensive look at how to use Eloquent aggregate functions such as count, sum, max, min, and avg (average).

Aggregates are functions which calculate a single result from a set of input values, like totalling up a column of numbers or finding the biggest one. Let’s dive into how to use aggregate functions with Laravel Eloquent to optimize, simplify, and clean up your database queries.

Getting Started with Counts

<?php
// Counting all users
$count = User::count();
echo $count;
?>

The simplest aggregate function is count(), which returns the number of records that match the query. In this example, we’re counting all users in our database.

Summing Values

<?php
// Summing user balances
$sum = User::sum('balance');
echo $sum;
?>

You might have a situation where you need to add up the values of a column. The sum() function does exactly this. Here we’re summing up the balance column of all users.

Finding Maximum and Minimum Values

<?php
// Getting the maximum balance
$max = User::max('balance');
echo $max;

// Getting the minimum balance
$min = User::min('balance');
echo $min;
?>

Somewhat related to summing is finding the maximum and minimum values within a column. The max() and min() functions return the highest and the lowest values found in a given column, respectively.

Calculating Averages

<?php
// Calculating the average balance
$avg = User::avg('balance');
echo $avg;
?>

The average, sometimes also known as the mean, can be found using the avg() function. This returns the average value of a numeric column.

Grouping Results

Where aggregations truly shine is when combined with the groupBy() clause. This allows you to perform aggregate functions on a data set that has been divided into groups.

<?php
 // Sum balances for users grouped by account type
 $sums = User::groupBy('account_type')
     ->selectRaw('sum(balance) as sum, account_type')
     ->pluck('sum','account_type');
 print_r($sums);
?>

In this snippet, the users are grouped by their account type, and the sum of balances for each account type is calculated and then plucked into an array indexed by the account types.

Combining Multiple Aggregates

You may want to run multiple aggregate queries in a single call, to save on database traffic.

<?php
 // Getting multiple aggregate values
 $stats = User::selectRaw(
     'count(*) as user_count,
     avg(balance) as avg_balance,
     max(balance) as max_balance'
 )->first();
 // Output results
 echo 'User Count: ' . $stats->user_count . '\n';
 echo 'Average Balance: ' . $stats->avg_balance . '\n';
 echo 'Max Balance: ' . $stats->max_balance;
?>

This technique can be particularly useful for creating summary reports where you need to display various statistics about a dataset.

Advanced Techniques

Sometimes you’ll want to apply different aggregate functions to subsets of data based on a condition. For that, Laravel provides the when() method. Additionally, leveraging complex joins, subqueries, and raw statements can allow for highly specific and performance-tuned aggregate queries.

<?php
 // Conditional aggregates
 $totalSales = Order::selectRaw('sum(amount) as total_sales')
     ->when($discounted, function($query) {
         return $query->where('discounted', true);
     })
     ->value('total_sales');
 ?>

In this example, we conditionally sum the amounts of orders based on whether a boolean variable $discounted is true.

And when combining Eloquent with raw SQL, you can perform even more customized aggregate functions, however, be cautious with raw queries, as they can expose you to SQL injection if not handled properly.

Conclusion

Aggregate functions are a powerful tool in the Eloquent ORM. By understanding and utilizing count, sum, max, min, and average, developers can write concise queries and perform complex data calculations and summaries with ease. Remember, though, with great power comes great responsibility. Use these functions wisely and always be mindful of the performance impacts on your database.