How can I get the division of two group by on SQL?

I’ve seen several posts to get the percentage of each row (using partition by), others about getting one row divided by another (using inner join with the same table), but I haven’t seem this problem, so I hope this post is not a problem.

I have the following table:

week            group      ID
2020-01-07      A          AUR1282
2020-01-07      A          BSF9382
2020-01-07      A          EOA9284
2020-01-07      B          AIF3984
2020-01-21      A          AIG3856
2020-01-21      B          PRT3950

And I want to get the percentage of IDs in group A. The result should be like this:

group      ID_perc
A          0.66

This should be pretty simple. But I got this far:

select t1.week, count(distinct t1.ID)::float/t2.total_ID as ID_perc
from table as A
inner joint (select count(distinct ID) as total_ID 
            from table where group='B') t2
on t1.week = t2.week
where t1.group = 'A'

This doesn’t solve my problem.

Any thoughts?

Thanks in advance

Answer

You can use this (based on your latest edit) –

 Select group, grp_count/total_cnt as perc
 from
 (Select group,count(*) as grp_count, min(total_cnt) as total_cnt
 from t
Cross Join (Select count(*) as total_cnt from t) t2
Group by group)

Advertisement

Answer