How to Calculate Median with MySQL in Laravel

Why it's not available in MySQL yet?

I know it's quite frustrating that we have to implement our own way to calculate the median in MySQL. But have you ever thought about why it's not available in MySQL yet? Let's take a minute to understand the reason.

Calculating the median is complex. It may require significant computational power for large datasets, as the data needs to be sorted. Then, the middle value must be selected—whether it's a single middle value in an odd-sized dataset or the average of two middle values in an even-sized one.

So, This is different from how typical aggregate functions like AVG or SUM work, as they are lightweight and can be computed in a single pass without requiring sorting or consuming much memory.

Create a query builder macro

The Laravel query builder allows you to define macros, which provide a fluent and expressive way to add custom methods for reuse.

Since median is also an aggregate function—similar to AVG—we can define it as a macro to make it reusable across queries.

To create the macro, we need to register it inside the boot method at app\Providers\AppServiceProvider.php.

use Illuminate\Database\Eloquent\Builder;

Builder::macro('median', function (string $column) {
	$column = DB::getQueryGrammar()->wrap($column);

	$subquery = $this->getQuery()
			->select([
					DB::raw("{$column} as value"),
					DB::raw("ROW_NUMBER() OVER (ORDER BY $column) AS row_num"),
					DB::raw('COUNT(*) OVER () AS total_count'),
			]);

	return DB::query()
			->fromSub($subquery, 'sub')
			->whereIn('row_num', [
					DB::raw('FLOOR((total_count + 1) / 2)'),
					DB::raw('FLOOR((total_count + 2) / 2)'),
			])
			->average('value') ?? 0;
});

Usage

Now we have our custom macro to calculate the median at the database level. Let's go ahead and calculate the first median value.

Employee::query()->median('salary');