How to display rows whose certain fields add up to 100?

Question

SQL

How to display rows where certain fields add up to 100 or more ?
There is such a request: You need to display those rows whose fields in total will give 100. For example , id amount 1 10 2 15 3 45 4 50 5 60 He must add in turn (in this case 1, 2, 3 and 4 ) until he gets result, will eventually get >= 100 Therefore output: id amount 1 10 2 15 3 45 4 50
SELECT amount FROM `table` ORDER BY `referral_id`

Answer 1

Request all rows. Choose them one by one, accumulating the amount. As soon as the sum has become more than 100 - stop sampling.

Answer 2

You can try like this:

WITH ref AS
(SELECT `referral_id` 
   FROM `table`
  GROUP BY `referral_id`
 HAVING sum(`amount`) >= 100)
SELECT t1.`referral_id`, t1.`id`, t1.`amount`
  FROM ref as t
 INNER JOIN `table` t1
         ON t1.`referral_id` = t.`referral_id`
ORDER BY t1.`referral_id`, t1.`id`