Getting the most frequent value in a column for a specific person
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 | +--------+-------+--------+---------+----------------+ 
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 
