Calculating percentages with GROUP BY query
WITH t1 AS (SELECT User, Rating, Count(*) AS n FROM your_table GROUP BY User, Rating)SELECT User, Rating, n, (0.0+n)/(COUNT(*) OVER (PARTITION BY User)) -- no integer divide!FROM t1;Or
SELECT User, Rating, Count(*) OVER w_user_rating AS n, (0.0+Count(*) OVER w_user_rating)/(Count(*) OVER (PARTITION BY User)) AS pctFROM your_tableWINDOW w_user_rating AS (PARTITION BY User, Rating);I would see if one of these or the other yields a better query plan with the appropriate tool for your RDBMS.
Alternatively, you can do the old-school way — arguably easier to grok:
select usr.User as User , usr.Rating as Rating , usr.N as N , (100.0 * item.N) / total.N as Pctfrom ( select User, Rating , count(*) as N from Results group by User , Rating ) usrjoin ( select User , count(*) as N from Results group by User ) total on total.User = usr.Userorder by usr.User, usr.RatingCheers!