Getting the most frequent value in a column for a specific person

Question

So I have a table where I collect each person's data.

+--------+-------+--------+---------+----------------+
| player | kills | deaths | assists | champSelection |
+--------+-------+--------+---------+----------------+
| Bob    | 1     | 3      | 4       | Horse          |
+--------+-------+--------+---------+----------------+
| Bob    | 2     | 7      | 5       | Horse          |
+--------+-------+--------+---------+----------------+
| Jake   | 5     | 5      | 5       | Dog            |
+--------+-------+--------+---------+----------------+
| Marie  | 2     | 3      | 4       | Dog            |
+--------+-------+--------+---------+----------------+
| Marie  | 1     | 1      | 9       | Horse          |
+--------+-------+--------+---------+----------------+
| Marie  | 6     | 7      | 2       | Dog            |
+--------+-------+--------+---------+----------------+

And I'm running

SELECT player, sum(kills), sum(deaths), sum(assists) 
FROM playerTable 
GROUP BY player 
ORDER BY player ASC;

How would I add onto my query where what their most frequent champSelection is? I'm trying to display my table as

+--------+-------+--------+---------+----------------+
| player | kills | deaths | assists | champSelection |
+--------+-------+--------+---------+----------------+
| Bob    | 3     | 10     | 9       | Horse          |
+--------+-------+--------+---------+----------------+
| Jake   | 5     | 5      | 5       | Dog            |
+--------+-------+--------+---------+----------------+
| Marie  | 9     | 11     | 15      | Dog            |
+--------+-------+--------+---------+----------------+

"How would I add onto my query where what their most frequent champSelection is?" Elaborate some more on this. What exactly are you trying to do? — Zircoz, Jul 11 '20 at 20:01
This is a recurrent question : I guess you should check this answe https://stackoverflow.com/questions/5657446/mysql-query-max-group-by — smwhr, Jul 11 '20 at 20:04
Display the table to show the sum of kills, deaths and assists, along with what their most frequent champSelection is for each player. — Jith, Jul 11 '20 at 20:05
@smwhr both are different, here he is trying to return the highest occurring value, there the question is to print the row with max timestamp — Zircoz, Jul 11 '20 at 20:14
What MySQL version used? Since MySQL 8.0 you can use window function for get desired result — Slava Rozhnev, Jul 11 '20 at 20:16
You can look this article about rank calculation: https://towardsdatascience.com/mysql-how-to-write-a-query-that-returns-the-top-records-in-a-group-12865695f436 — Slava Rozhnev, Jul 11 '20 at 20:40

score 1 · Accepted Answer · answered Jul 13 '20 at 07:33

Try this:

SELECT player, SUM(kills), SUM(deaths), SUM(assists), frequency
FROM
(
    SELECT player, SUM(kills) AS kills, SUM(deaths) AS deaths, SUM(assists) AS assists, champSelection, COUNT(*) AS frequency
    FROM playerTable
    GROUP BY player, champSelection
    ORDER BY frequency DESC
) AS inner_table
GROUP BY player

Getting the most frequent value in a column for a specific person

1 Answers1