CODEWITHSUNDEEP
mysqlsqlphpmyadminpercentile
Quark
Quark

Reputation: 59

MySQL - Percentile calculation and update it in other column in the same table

I have a table in MySQL (phpMYAdmin) with the following columns

MySQL Table

I am trying to determine the percentile for each row and update that value in the G1Ptile column. G1Ptile column is the percentile calculation based on G1%. I am using the following based on John Woo's answer given here 

SELECT  `G1%`, 
       (1-ranks/totals)*100 Percentile FROM (
SELECT  distinct `G1%`,
        @rank:=@rank + 1 ranks,
        (SELECT COUNT(*) FROM PCount) totals
FROM    PCount a,
        (SELECT @rank:=0) s
ORDER   BY `G1%` DESC ) s;

and get the following output

Output

The output is in a select statement, I want to be able to update it to the G1Ptile column in my table, however I am unable to update it using 

UPDATE `PCount` SET `G1Ptile`= --(All of the select query mentioned above)

Can you please help with modifying the query/suggest an alternative so that I can use the percentile values obtained using the above query and update it into G1Ptile in the same table. One more problem I have is that there are two 20% values in G1%, however the percentile assigned to one is 20 and other is 30. I want both of them to be 20 and the next row in the series to be 30.

Upvotes: 0

Views: 403

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I would write your calculation as:

SELECT `G1%`, 
       (1 - ranks / totals) * 100 as Percentile
FROM (SELECT `G1%`,
              (@rank := @rank + 1) ranks,
              (SELECT COUNT(*) FROM PCount) as totals
      FROM (SELECT DISTINCT `G1%`
            FROM PCount
            ORDER BY `G1%` DESC
           ) p CROSS JOIN
           (SELECT COUNT(*) as totals, @rank := 0
            FROM Pcount
           ) params
     ) p;

I made certain changes more consistent with how MySQL processes variables. In particular, the SELECT DISTINCT and ORDER BY are in a subquery. This is necessary in more recent versions of MySQL (although in the most recent you can use window functions).

This can now be incorporated into an update using JOIN:

UPDATE PCount p JOIN
       (SELECT `G1%`, 
                (1 - ranks / totals) * 100 as Percentile
        FROM (SELECT `G1%`,
                      (@rank := @rank + 1) ranks,
                      (SELECT COUNT(*) FROM PCount) as totals
              FROM (SELECT DISTINCT `G1%`
                    FROM PCount
                    ORDER BY `G1%` DESC
                   ) p CROSS JOIN
                   (SELECT COUNT(*) as totals, @rank := 0
                    FROM Pcount
                   ) params
             ) pp
       ) pp
       ON pp.`G1%` = p.`G1%`
    SET p.G1Ptile = pp.percentile;

Upvotes: 1

Related Questions