MySQL Calculate Percentage from one table

Question

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 1

 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;

MySQL Calculate Percentage from one table

Sign up