Calculate mode grouped by a column

Question

+--------+-------+
| client | price |
+--------+-------+
|     54 |    25 |
|    648 |    35 |
|     54 |    10 |
|    648 |     8 |
|     54 |    25 |
|    648 |    35 |
+--------+-------+

Lets say above is how my table schema is set up and I want to calculate the most frequent price value per client eg.

+--------+-------+
| client | price |
+--------+-------+
|     54 |    25 |
|    648 |    35 |
+--------+-------+

I'm having a hard time accomplishing this in MySQL. I've done it in PHP like so:

$clientPrices = $this->database->select('design', [
    'clientid',
    'price'
]);

$pricesByClients = [];
foreach ($clientPrices as $value) {
    $pricesByClients[$value['clientid']][] = $value['price'];
}

foreach ($pricesByClients as $key => $value) {
    $priceCount = array_count_values($value);
    $mode = array_search(max($priceCount), $priceCount);
    $pricesByClients[$key] = $mode;
}

return $pricesByClients;

But, this is slow and I was hoping if I can either make this a bit efficient or do it in SQL.

Edit: It's MySQL 5.* and not 8.

Answer 1

Unfortunately, MySQL has no built-in function that computes the mode().

If you are using MySQL 8.0, you can use window functions and aggregation:

select client, price
from (
    select client, price, rank() over(partition by client order by count(*) desc) rn
    from mytable
    group by client, price
) t
where rn = 1

In earlier versions, and option is to filter with a having clause and a correlated subquery

select client, price
from mytable t
group by client, price
having count(*) = (
    select count(*)
    from mytable t1
    where t1.client = t.client
    group by t1.price
    order by count(*) desc
    limit 1
)