-1

I want to calculate result percentage in SQL from the this data for a each period. 

Period  Result
1        Green
1        Blue
1        Blue
1        Red
1        Blue
1        Blue
1        Blue
2        Green 
2        Green 
2        Green
2        Blue
2        Red
2        Red

Expected result .. 

Period  Result  Percentage
1        Blue     72%
1        Green    9%
1        Red      9%
2        Blue     17%
2        Green    50%
2        Red      33%
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
Durrani Muhammad
  • 1
  • 3
    [What have you tried?](http://whathaveyoutried.com) Which RDBMS are you using? –  Sep 26 '12 at 21:45

2 Answers2

2

COUNT by Period first, join the result with the original table again, grouped by both Period and Result, and then just do a division to obtain the percentage:

SELECT t.Period, t.Result, ((COUNT(t.Result) / Cnt) * 100) Percentage
FROM table t 
     INNER JOIN (SELECT Period, COUNT(*) Cnt
                 FROM table
                 GROUP BY Period) period_cnt 
     ON t.Period = period_cnt.Period
GROUP BY t.Period, t.Result

You may need to adjust the rounding, and use CONCAT to add the character % to your output, but that should be fairly easy.

Also, your average for the first period is wrong, it should add up to 100. Green and Red should have the value 14.

DEMO.

João Silva
  • 89,303
  • 29
  • 152
  • 158
1

Something like this (ANSI SQL):

select period,
       result,
       (count(result) / total_period) * 100 as result_percent
from (
  select period, 
         result,
         count(*) over (partition by period) as total_period
  from periods  
) as t
group by period, total_period, result
order by period, result;

Depending on your DBMS you might need to cast the integer values to a decimal in order to see fractional values.

Demo: http://sqlfiddle.com/#!1/2ec4f/1