sql - Ratings query - percentage of ratings for specified id over ratings for ALL ids -

i have inherited following query, gets average rating specified "try" (rugby jargon touchdown). can still work it.

select i.id, i.title,         (             case                 when                     count( r.rating ) > 0                                     (                         sum( r.rating ) / count( r.rating )                     )                 else                     0             end         ) rating,          coalesce( er.id, 0 ) has_existing_rating             (             select 1 id, 'try 1 – israel dagg v chiefs.' title union             select 2 id, 'try 2 – israel dagg v chiefs.' title union             select 3 id, 'try 3 – leilia masaga v crusaders.' title union             select 4 id, 'try 4 – israel dagg v chiefs.' title union             select 5 id, 'try 5 – fred flintstone v hurricanes.' title union             select 6 id, 'try 6 – israel dagg v chiefs.' title union             select 7 id, 'try 7 – israel dagg v chiefs.' title         )      left outer join         trypoll r     on         i.id = r.try_id      <!--         join rating table again see if current         user has rated given try.     -->     left outer join         trypoll er     on         (                 er.try_id = i.id             ,                 er.ip_address = '#cgi.remote_addr#'             ,                 er.user_agent = '#cgi.http_user_agent#'         )      group         i.id,         r.try_id,         er.id,         i.title     order         i.id asc 

so, given following table (rating = 1 means single vote in case) ....

trypoll

 id     try_id     rating     ip_address     user_agent     ------------------------------------------------------     1      2          1          58.28.220.51   mozilla/5.0 blah     2      2          1          58.28.220.52   mozilla/5.0 blah     3      6          1          58.28.220.53   mozilla/5.0 blah     4      4          1          58.28.220.54   mozilla/5.0 blah 

... query return average rating of (1 + 1) / 2 = 1 try_id #2

however, need adjust query return percentage of ratings particular try on ratings tries. i.e., in above example, determine percentage of ratings tries attributed try_id #2

how accomplish this?

you try solution:

declare @try_id int; set @try_id=2;  select  r.try_id,         sum(r.rating) ratings_per_try,         sum(sum(r.rating)) over() ratings_overall,         sum(r.rating)*1.0 / nullif(sum(sum(r.rating)) over(), 0) percent_try         table r group r.try_id