Calculating group percentage in SQL-CodePudding

I have a sample db table with columns 'team' and 'result' that stores match results 'W' (win) or 'L' (loose) for teams 'A' and 'B':

team|result
A   |W
A   |W
A   |W
A   |L
B   |W
B   |W
B   |L
B   |L

I can get the number of wins/losses per team by grouping by team and result:

sqlite> select team,result,count(*) from results group by team,result;

A|L|1
A|W|3
B|L|2
B|W|2

However, I would also like to get a percentage of win/loss per team:

A|L|1|25
A|W|3|75
B|L|2|50
B|W|2|50

I have not succeeded in figuring out how to do this in SQL. I have managed to do this programmatically with a python programme that queries the db via the sqlite api, then loops over the result set and creates a variable to store the total count per group and then calculate percentage, etc

Can this be achieved directly in SQL?

Thanks

CodePudding user response：

We can use SUM() as an analytic function:

SELCET team, result, COUNT(*) AS cnt,
       100.0 * COUNT(*) / SUM(COUNT(*)) OVER (PARTITION BY team) AS pct
FROM results
GROUP BY team, result;