On this query here, I get the following result in the image provided below. However I want to take it a step further and turn the count into a ratio of wins / total wins.
However when I use the second query (at the bottom) I get an error invalid use of the group function.
To explain, the count is based on a grouping of each unique combination of columns in the query
Can someone explain what i’m doing wrong please?
SELECT summoner_id, monster_1_id, monster_2_id, monster_3_id, monster_4_id, monster_5_id, monster_6_id, Count(*)/ ( SELECT SUM(Count(*)) ) AS Ratio FROM battledata.history GROUP BY summoner_id, monster_1_id, monster_2_id, monster_3_id, monster_4_id, monster_5_id, monster_6_id ;
SELECT summoner_id, monster_1_id, monster_2_id, monster_3_id, monster_4_id, monster_5_id, monster_6_id, Count(*)/ ( SELECT SUM(Count(*)) ) AS Ratio FROM battledata.history GROUP BY summoner_id, monster_1_id, monster_2_id, monster_3_id, monster_4_id, monster_5_id, monster_6_id ;
Advertisement
Answer
You need to use COUNT(*)
as a subquery (if there is a where clause then it needs to match in both queries):
SELECT summoner_id, monster_1_id, monster_2_id, monster_3_id, monster_4_id, monster_5_id, monster_6_id, COUNT(*) / (SELECT COUNT(*) FROM battledata.history) AS Ratio FROM battledata.history GROUP BY summoner_id, monster_1_id, monster_2_id, monster_3_id, monster_4_id, monster_5_id, monster_6_id
Edit:
If using MySQL 8 or later you can use window functions. Your original query will work after correcting the syntax:
SELECT summoner_id, monster_1_id, monster_2_id, monster_3_id, monster_4_id, monster_5_id, monster_6_id, COUNT(*) / SUM(COUNT(*)) OVER () AS Ratio FROM battledata.history GROUP BY summoner_id, monster_1_id, monster_2_id, monster_3_id, monster_4_id, monster_5_id, monster_6_id