mysql中两个count值相除，如何合并？

Question

如何合并如下两条语句实现两个结果相除保留2位小数的百分比。
语句1:
select count(id) as total_pass from table1 where status = 'pass' group by orderid，product
语句2：
select count(sn) as total_sn from table1 group by group by orderid，product
实现结果：
orderid | product | total_pass | total_sn | rate
201 | 产品A | 50| 50| 100.00%
203 | 产品B | 60| 120 | 50.00%

Answer 1

group by 的维度一样,没必要分两次查,可以直接在count里写判断条件

select orderid,product,
count(case when status='pass' then id end) total_pass,
count(sn) total_sn,
concat(round(count(case when status='pass' then id end)/count(sn)*100,2),'%') rate
from table1 group by group by orderid,product