CODEWITHSUNDEEP
sqlms-accessms-access-2010
barker
barker

Reputation: 1055

calculate sql percentage of column a grouped by column b

My data is as follows:

name | color

joe | red  
joe | red
tom | blue  
joe | green  
tom | red  
mike | yellow  
mike | green  
mike | red

How do I write SQL to generate the following?

name | color | percent

joe | red | 66.6%
tom | blue | 50%
joe | green | 33.3%
tom | red | 50%
mike | yellow | 33.3%
mike | green | 33.3%
mike | red | 33.3%

Here is the code i was trying in microsoft access SQL 

select name, color, (count(color)*100.0/count(*)) as 'perc'
from mytable
group by name, color;

Upvotes: 0

Views: 234

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You need to get the total for each color to get the percent. This requires joining in another value. I think the following will work in Access:

select nc.name, nc.color, nc.cnt * 100 / n.cnt & '%'
from (select name, color, count(*) as cnt
      from mytable as t 
      group by name, color
     ) as nc inner join
     (select name, count(*) as cnt
      from mytable as t
      group by name
     ) as n
     on nc.name = n.name;

Upvotes: 1

Related Questions