0

I have a temporary table which gives me these results.

Question      Option    TotalUsers 
Question1    Q1Option1    4             
Question1    Q1Option2    0              
Question1    Q1Option3    4                           
Question1    Q1Option4    0              
Question2    Q2Option1    2             
Question2    Q2Option2    2             
Question2    Q2Option3    2             
Question2    Q2Option4    2

Query

SELECT Question, Option, TotalUsers FROM @TemporaryResultTable

I need to calculate percentage of TotalUsers out of TotalUsers with in that question it means grouped by that question like shown below.

I tried in the below way but doesn't work.

SELECT Question, Option, TotalUsers, SUM([TotalUsers]) * 100/NULLIF(SUM([TotalUsers]), 0) OVER (PARTITION BY Question) AS 'Percentage' FROM @TemporaryResultTable 
GROUP BY Question, Option, TotalUsers

any help on this

Juan
  • 17
  • 7

1 Answers1

0

You can use window functions:

select question, option, totalusers,
    100.0 * totalusers / sum(totalusers) over(partition by question) as percentage
from @temporaryresulttable
GMB
  • 216,147
  • 25
  • 84
  • 135