php - Calculate mode grouped by a column
Get the solution ↓↓↓
+--------+-------+
| 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 frequentprice value perclient 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
Solution:
Unfortunately, MySQL has no built-in function that computes themode().
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 ahaving 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
)
Answer
Solution:
For MySql 8.0+ you can use row_number() window function:
select t.client, t.price
from (
select client, price,
row_number() over (partition by client order by count(*) desc) rn
from tablename
group by client, price
) t
where t.rn = 1;
For previous versions you can use a correlated subquery:
select distinct t.client, t.price
from tablename t
where (t.client, t.price) = (
select client, price
from tablename
where client = t.client
group by client, price
order by count(*) desc
limit 1
);
See the demo.
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: using $this when not in object context
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.