with a as (select distinct exam_id from exam_record where submit_time is null) select exam_id, count(case when submit_time is null then 1 else null end) as incomplete_cnt, round(count(case when submit_time is null then 1 else null end)/count(*) ,3) from exam_record where exam_id in (select exam_id from a) group by exam_id