MySQL Question
MySQL Calculate Percentage from one table
I need to calculate percentage for top 20 failed users, Need it in spring java with hibernate.
+--------------+--------+------------+
| id | result | code | techUser_id
+--------------+--------+------------+
| 1 | fail | 23442 | 2
| 2 | fail | 56432 | 5
| 3 | fail | 98745 | 2
| 4 | fail | 65478 | 5
| 5 | fail | 36448 | 2
| 6 | fail | 87745 | 5
+--------------+--------+------------+
Expected output: list of top 20 failed with max % of total number of records by per user
I am not sure about Query what it should be, so please help me to find the solution.
something i have, I know its not correct :--
@Query("select count(inspectionResult), techUser.id,techUser.username FROM inspection where techUser.id != '' and result ='FAIL' group by techUser.id order by 1 desc ")
List<Object> getFailedInspectionStatForTechnician();
Thanks
Answer Source
select techUser.id, count(*)/(SELECT COUNT(*) from inspection) * 100 as perc
from inspection
where techUser.id != ''
and result ='FAIL'
group by techUser.id
order perc limit 20;