I have a table where users can enter a score of a game. Home score and away score in a separate column. When I look for GameID = 45, i would like to receive a list of scores and corresponding percentages. I have certainly searched for similar questions. Like this and others, How to calculate percentage with a SQL statement

But I am stuck with 2 columns (Homescore and awayscore) that I need to compare.

Tablename: Scores

GameScoreID	GameID	HomeScore	AwayScore	CharID
1	45	2	1	452
2	45	2	1	855
3	75	3	0	485
4	85	0	2	757
5	45	1	1	185
6	45	0	1	825
7	45	1	1	850
8	45	1	1	385
9	45	2	1	895
10	45	2	1	285
11	75	3	0	285
12	45	2	1	458
13	45	2	1	657

I have a table where users can enter a score of a game. Home score and away score in a separate column. When I look for GameID = 45, i would like to receive a list of scores and corresponding percentages. I have certainly searched for similar questions. Like this and others, How to calculate percentage with a SQL statement

But I am stuck with 2 columns (Homescore and awayscore) that I need to compare.

Tablename: Scores

GameScoreID	GameID	HomeScore	AwayScore	CharID
1	45	2	1	452
2	45	2	1	855
3	75	3	0	485
4	85	0	2	757
5	45	1	1	185
6	45	0	1	825
7	45	1	1	850
8	45	1	1	385
9	45	2	1	895
10	45	2	1	285
11	75	3	0	285
12	45	2	1	458
13	45	2	1	657

GameID = 45 - Total of 10 votes. Score 2 -1 = Total of 6 votes = 60%. Score 1 -1 = Total of 3 votes = 30%. Score 0 -1 = Total of 1 votes = 10%

    SELECT HomeScore , AwayScore , CONCAT(count(GameID = ‘45’) * 100.0 / SUM(CASE WHEN 
    GameID = 45 AND (“stuck here” HomeScore, AwayScore) THEN 1 ELSE 0 END)
    ,'%')
    AS Percentage
    FROM Scores Where GameID = 45 GROUP BY Percentage;

This is what i want as result.

HomeScore	AwayScore	Percentage
2	1	60%
1	1	30%
0	1	10%

• sql

Share Improve this question Follow edited Mar 20 at 11:34 Jonas Metzler 6,16033 gold badges88 silver badges2222 bronze badges asked Mar 20 at 11:33 Dimko 696Dimko 696 4511 silver badge66 bronze badges 5

• I don't get what exactly is your problem with the solution proposed in the question you linked. The answer there is obvously correct, see this db<>fiddle with your sample data. Ok, you might want to round the percentage to int (depends on the RDBMS you use how to do that), but generally, it's quite simple? – Jonas Metzler Commented Mar 20 at 11:46
• This question is similar to: How to calculate percentage with a SQL statement. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. – Jonas Metzler Commented Mar 20 at 11:51
• Which dbms are you using? The above query is product specific in several ways. – jarlh Commented Mar 20 at 11:54
• I thought there had to be an extra condition because of the homescore and awayscore belonging together. I was a bit stuck there. I don't know what "RDBMS" or "dbms" means. – Dimko 696 Commented Mar 20 at 13:32
• @Dimko696 Please read Why should I tag my RDBMS, it tells you both what RDBMS means and also why it's important. – Jonas Metzler Commented Mar 20 at 13:36

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Grouping Votes by Score:

Let’s group those votes by their (HomeScore, AwayScore):

(2, 1) appears 6 times

(1, 1) appears 3 times

(0, 1) appears 1 time

Now calculate percentages:

You take each group's count and divide it by the total (10):

(2,1): 6 / 10 = 0.60 → 60%

(1,1): 3 / 10 = 0.30 → 30%

(0,1): 1 / 10 = 0.10 → 10%

You just need to group by HomeScore and AwayScore, count the occurrences, and calculate the percentage based on the total votes for that GameID.

SELECT 
    HomeScore,
    AwayScore,
    CONCAT(ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 0), '%') AS Percentage
FROM Scores
WHERE GameID = 45
GROUP BY HomeScore, AwayScore;