ms access - sql subqueries, grouping and a bit of math -

this counts items in b , groups on a:

select a, count(*) [count all] mytable  group a; 

this counts 1s in b , groups on a:

select a, count(*) [count ones] mytable      mytable.[b]='1'  group a; 

how put both columns (all , 1s) , additionally, how show percentage in row

table columns:

[a] [count all] [count ones] [ones/all]

since ms-access can use iif instead of case

select       table1.a,       sum(iif([b]=1,1,0)) count1,       count(table1.a) total,       (sum(iif([b]=1,1,0))/ count(table1.a)) * 100 percentofones       table1 group       table1.a;