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

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 ;

CodePudding user response：

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

CodePudding user response：

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

This should make what you need. If you need to add a WHERE clause just remember to add the same in both queries