MySQL - Calculate percent of total by group

Question

State     ID     Value     Result1 (%)     Result2 (Rank)
MA        AAA    44        0.24            2
MA        BBB    12        0.07            4
MA        CCC    90        0.50            1
MA        DDD    34        0.19            3
CT        EEE    200       0.10            5
CT        FFF    300       0.15            4
CT        GGG    400       0.20            3
CT        HHH    500       0.25            2
CT        III    600       0.30            1

Is there a way to calculate percent of totals BY GROUP (in this case by "State") and then rank them in descending order?

I have the first 3 columns and I want to calculate the last 2 columns in MySQL.

I have seen plenty of examples of calculating percentages across a single group but I can't find an example where percentages are calculated across multiple groups AND are then ranked. Then I would like to limit the results to the top N (top 3 for example).

@Rams, It not really percent in the 4th column but its: _24.44%_ from 44/180. 180 is the Sum( Value ) group by State — Drag and Drop, Jun 13 '17 at 13:36
When you just have problems with the `and rank them` part, post the query you got for the percentage part. Why should we work harder than necessary to help you? — fancyPants, Jun 13 '17 at 13:40

score 1 · Answer 1 · edited Jun 13 '17 at 14:24

You can get result by:

SELECT * 
FROM (
    SELECT tt.*
    ,@statewiserank := IF(@state_v = State, @statewiserank + 1, 1) AS statewiserank
    ,@state_v := State 
    FROM (
        SELECT State,ID,Value, value/
            (SELECT sum(value) 
            FROM yourtable it 
            WHERE it.state=ot.state 
            GROUP BY state) result1_perc
        FROM yourtable ot 
        ORDER BY state, result1_perc DESC) tt
    ) 
WHERE statewiserank <= 3

MySQL - Calculate percent of total by group

1 Answers1