Calculating percentages with GROUP BY query

Question

I have a table with 3 columns which looks like this:

File    User     Rating (1-5) ------------------------------ 00001    1        3 00002    1        4 00003    2        2 00004    3        5 00005    4        3 00005    3        2 00006    2        3 Etc. 

I want to generate a query that outputs the following (for each user and rating, display the number of files as well as percentage of files):

User    Rating   Count   Percentage ----------------------------------- 1       1         3      .18 1       2         6      .35 1       3         8      .47 2       5         12     .75 2       3         4      .25 

With Postgresql, I know how to create a query that includes the first 3 columns using the following query, but I can't figure out how to calculate percentage within the GROUP BY:

SELECT     User,     Rating,     Count(*) FROM     Results GROUP BY     User, Rating ORDER BY     User, Rating 

Here I want the percentage calculation to apply to each user/rating group.

Answer 1

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 pct FROM your_table WINDOW 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.

Answer 2

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 * usr.N) / total.N as Pct from ( select User, Rating , count(*) as N        from Results        group by User , Rating      ) usr join ( select User , count(*) as N        from Results        group by User      ) total on total.User = usr.User order by usr.User, usr.Rating 

Cheers!