mysql - how to group by a field that has both select and count -

i wondering how group field has both select count() , count() statement. know have put select fields in group wont let me because of second count() statement in field.

create table c as( select a.id, a.date_id,  (select count(b.hits)*1.00 b.hits >= '9')/count(b.hits) percent **<--error here a join b b on a.id = b.id group 1,2,3) no data primary index(id); 

this error:

[sqlstate hy000] group , with...by clauses may not contain  aggregate functions. error code: 3625  

when add select second count in third line 1 or 0 not right.

`((select count(b.hits)*1.00 b.hits >= '9')/(select count(b.hits))) as` percent 

do need self join instead or there way can use nested queries?

it looks need group on 2 columns, not 3, plus shouldn't need sub-select:

create table c as( select a.id, a.date_id,  sum(case when b.hits >= '9' 1 else 0 end)/count(b.hits) percent  a join b b on a.id = b.id group 1,2) no data primary index(id);