Aggregates — count(), max(), min(), avg(), sum()
Concept
The Query Builder's aggregate methods execute immediately and return scalar values. They bypass the get() / first() pipeline and translate to aggregate SQL functions.
count(string $columns = '*'): SELECT COUNT(*). With column: COUNT(column) — counts non-NULL values only.
max(string $column) / min(string $column): Maximum/minimum value. Returns the scalar value, not a row.
avg(string $column) / average(string $column): Average (alias).
sum(string $column): Sum of all values. Returns 0 if no rows match (not null).
exists() / doesntExist(): SELECT EXISTS(SELECT 1 FROM ... WHERE ...). Returns bool. Always prefer this over count() > 0 — EXISTS short-circuits on the first match.
value(string $column): Single column value from first row. Not strictly an aggregate but often grouped with them.
Multiple aggregates in one query: Use selectRaw + get() to compute multiple aggregates in a single SQL round-trip instead of N separate aggregate calls.
DB::table()->aggregate(string $function, array $columns = ['*']): The underlying aggregate method. Called by count(), max(), etc.
Eloquent aggregate methods: Same API — User::count(), Order::sum('total'), User::max('age'). These run on the model's table with any active global scopes applied.
Code Example
<?php
use Illuminate\Support\Facades\DB;
use App\Models\Order;
// Scalar aggregates
$total = DB::table('orders')->count(); // all rows
$active = DB::table('users')->where('active', true)->count(); // with filter
$countNonNull = DB::table('users')->count('email'); // non-NULL emails
$maxTotal = DB::table('orders')->max('total'); // highest order value
$minTotal = DB::table('orders')->where('status', 'completed')->min('total');
$avgTotal = DB::table('orders')->avg('total');
$revenue = DB::table('orders')->where('status', 'completed')->sum('total'); // 0 if empty
// exists — more efficient than count() > 0
if (DB::table('users')->where('email', $email)->exists()) {
// email taken
}
if (DB::table('orders')->where('user_id', $userId)->doesntExist()) {
// first-time buyer
}
// Multiple aggregates — ONE query
$stats = DB::table('orders')
->where('status', 'completed')
->selectRaw('
COUNT(*) as total_orders,
SUM(total) as revenue,
AVG(total) as avg_order,
MAX(total) as largest_order,
MIN(total) as smallest_order,
COUNT(DISTINCT user_id) as unique_buyers
')
->first();
// $stats->revenue, $stats->total_orders, etc.
// Eloquent — same API, global scopes applied
Order::count(); // total orders (SoftDeletes scope applied)
Order::where('status', 'completed')->sum('total');
Order::withTrashed()->count(); // including soft-deleted
// Per-group aggregates (GROUP BY)
$byStatus = DB::table('orders')
->select('status')
->selectRaw('COUNT(*) as count, SUM(total) as revenue')
->groupBy('status')
->get()
->keyBy('status'); // ['pending' => {...}, 'completed' => {...}]
$completedRevenue = $byStatus['completed']->revenue ?? 0;