select department,concat(round(sum(count_newma)/sum(count_1)*100,1),'%') as ratio
from
(
select s.staff_id,department,newma,1 as count_1,
case
when newma > 9.5 then 1
else 0
end as count_newma
from staff_tb as s
join
(
select staff_id,timestampdiff(second,first_clockin,last_clockin)/3600 as newma from attendent_tb
) as a
on s.staff_id = a.staff_id
) as t1
group by department
order by sum(count_newma)/sum(count_1) desc