Get top n records accounting for >= 90% amount for each group

Question

I have recently read and implemented the answer to a similar question here and would like to see if it can be taken one step further.

Link to original question

Using a similar sample data set:

+--------+-------+---+
| ID | Group | Amount|
+--------+-------+---+
| 1  | 1     | 50    |
| 2  | 1     | 25    |
| 3  | 1     | 15    |
| 4  | 1     | 10    |
| 5  | 1     | 0     |
| 6  | 1     | 0     |
| 7  | 2     | 60    |
| 8  | 2     | 20    |
| 9  | 2     | 10    |
| 10 | 2     | 5     |
| 11 | 2     | 5     |
| 12 | 2     | 0     |
| 13 | 3     | 90    |
| 14 | 3     | 10    |
+--------+-------+---+

Using the code listed below (Thanks to @Bluefeet from the previous question), the outcome is this:

+--------+-------+---+
| 1   | 1     | 50   |
| 2   | 1     | 25   |
| 7   | 2     | 60   |
| 8   | 2     | 20   |
| 13  | 3     | 90   |
| 14  | 3     | 10   |
+--------+-------+---+

set @num := 0, @group := '';

select id, `group`, amount
from 
(
   select id, `group`, amount,
      @num := if(@group = `group`, @num + 1, 1) as row_number,
      @group := `group` as dummy
  from mytable
  order by `Group`, amount desc, id
) as x 
where x.row_number <= 2;

What I would like to do is select the IDs by Group, by Amount desc, until >=90% contribution for the group is met. Ideally, it should look like this:

+--------+-------+---+
| 1   | 1     | 50   |
| 2   | 1     | 25   |
| 3   | 1     | 15   |    
| 7   | 2     | 60   |
| 8   | 2     | 20   |
| 9   | 2     | 10   |
| 13  | 3     | 90   |
+--------+-------+---+

Notice how for each group the sum of the amount for each group is 90 (the amounts for each group add up to 100 for this example).

Thanks in advance, and let me know if there's more information that may be of help.

Answer 1

Presumably you mean 90% of the sum of "amount". Start by doing a cumulative sum of amount:

  select id, `group`, amount,
         @cumsum := if(@group = `group`, @cumsum + amount, 0) as cumsum,
         @group := `group` as dummy
  from mytable
  order by `Group`, amount desc, id

Then, you need the total for each group for the 90% calculation. Let's do this by joining in this query to an aggregation query:

select id, `group`, amount, cumsum
from (select id, `group`, amount,
             @cumsum := if(@group = `group`, @cumsum + amount, 0) as cumsum,
             @group := `group` as dummy
      from mytable
      order by `Group`, amount desc, id
     ) t join
     (select `group`, sum(amount) as tot
      from mytable
      group by `group`
     ) tg
     on t.`group` = tg.`group`
where cum <= 0.9 * tot;

Or, if you want the first value after 90%, then use:

where (cumsum - amount) < 0.9 * tot