Ratio of matching rows per group

Question

I'd like to calculate the ratio of items in a group that fulfil certain criteria out of the total number of items in that group. I've already solved this but am curious to know if my solution is optimal, as the query takes a problematically long time over my large (10m+) dataset.

Here is what I have in its simplest form:

create table #tableA 
(
 id int IDENTITY(1,1),
 groupid int,
 flag bit,
 CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED 
 (
  [id] ASC
 )
)

insert into #tableA (groupid,flag) values (1,0),(1,0),(1,1),(2,0),(2,1)

select 
 a.groupid ,
 cast(totalCount as float)/count(*) as ratio
from 
 #tableA a 
 join 
 (
  select 
   groupid,
   COUNT(*) totalCount 
  from 
   #tableA 
  where 
   flag=1 
  group by 
   groupid
 ) b on a.groupid=b.groupid 
group by 
 a.groupid,
 b.totalCount

drop table #tableA

Is there a more efficient way to write this query?

Answer 1

Assuming flag is 0 or 1, this should work:

select groupid ,
       cast(sum(flag) as float)/count(*) as ratio
from tableA
group by groupid

If flag can take other values, a CASE or IF() should help make SUM work anyway.

Answer 2

select groupid , AVG(cast(flag as float)) as ratio from tableA group by groupid