with t as ( select '완료' state, 'abc' name from dual union all select '진행' state, 'abc' name from dual union all select '완료' state, 'abc' name from dual union all select '완료' state, 'bbb' name from dual union all select '완료' state, 'bbb' name from dual ) select count(decode(state, '완료', 1)) finished, count(*) total, name from t group by name order by name asc