krishan krishan - 5 months ago 20
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
 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;
Comments