sql - Count percentage of certain column values and add it to a new column -

i have following table

[table_orangeisnewblack] id  |   name    |   color   |   redpercent ------------------------------------------ 1   |   donald  |   orange  |    2   |   hillary |   white   |    3   |   barack  |   black   |    4   |   bernie  |   grey    |    1   |   donald  |   red     |    2   |   hillary |   red     |    3   |   barack  |   black   |    4   |   bernie  |   grey    |    1   |   donald  |   red     |    2   |   hillary |   blue    |    3   |   barack  |   red     |    4   |   bernie  |   purple  |    

i need add percentage value presenting how person 'red'

---

donald's record

1       donald      orange       1       donald      red          1       donald      red          

redpercent : (2 / 3 ) * 100 = 66,66

---

hillary

2       hillary     white        2       hillary     red          2       hillary     blue         

'blue' , 'purple' not count, :

redpercent : (1 / 2) * 100 = 50,00

---

barack

3       barack      black        3       barack      black        3       barack      red 

redpercent : (1 / 3) * 100 = 33,33

---

bernie

4       bernie      grey         4       bernie      blue         4       bernie      purple       

'blue' , 'purple' not count

redpercent : (0 / 1) * 100 = 00,00

result needed:

[table_orangeisnewblack] id  |   name    |   color   |   redpercent ------------------------------------------ 1   |   donald  |   orange  |   66,66 2   |   hillary |   white   |   50,00 3   |   barack  |   black   |   33,33 4   |   bernie  |   grey    |   00,00 1   |   donald  |   red     |   66,66 2   |   hillary |   red     |   50,00 3   |   barack  |   black   |   33,33 4   |   bernie  |   grey    |   00,00 1   |   donald  |   red     |   66,66 2   |   hillary |   blue    |   50,00 3   |   barack  |   red     |   33,33 4   |   bernie  |   purple  |   00,00 

i've tried :

;with cte     (         select  id,                      -- division: count of reds  / count of persons rows                      ((select count(id) table_orangeisnewblack                     color = 'red' )                     /                            (select count(id) table_orangeisnewblack                     ( color  <> 'blue' ,                             color  <> 'purple' ))                     )                     redprcnt                 -- end of division         table_orangeisnewblack     )     update t1         set redpercent = cte.redprcnt         table_orangeisnewblack t1      inner join cte on(t1.id = cte.id) 

select clause...

select  id,              -- division: count of reds  / count of persons rows              ((select count(id) table_orangeisnewblack             color = 'red' )             /                    (select count(id) table_orangeisnewblack             ( color  <> 'blue' ,                     color  <> 'purple' ))             )             redprcnt         -- end of division table_orangeisnewblack 

...gives zeros redprcnt

should use group @ point?

any suggestions?

thanks in advance.

count results in integer, count/count integer division truncates , base query tries calculate overall percentage instead of % per group.

additionally calculation can simplified using conditional aggregation:

select id,         -- division: count of reds  / count of persons rows     100.00 * count(case when color = 'red' id end)           / nullif(count(case when color  <> 'blue' , color  <> 'purple'  id end), 0) redprcnt table_orangeisnewblack group id 

the case returns null non-matching rows not counted.

edit:

if need repeat percentage each row can switch windowed aggregate instead:

select id, name, color,     100.00 * count(case when color = 'red' id end) over(partition name)           / nullif(count(case when color  <> 'blue' , color  <> 'purple'  id end) over(partition name), 0) redprcnt  table_orangeisnewblack