Skip to content
Advertisement

on SQL can I have a ratio column based on a count of multiple parameters?

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 ;

enter image description here

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
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement