Using SQL to select all possible set of rows using value of a column

I’m wondering if such thing is possible with SQL only. What I’m trying to achieve here is the following:

An SQL table with the following column:

------------
| DURATION |
|----------|
|     5    |
|    14    |
|     3    |
|    25    |
|     .    |
|     .    |
|     .    |

I want to select all possible set of rows satisfying the sum of DURATION from each row being lesser than or greater than a given value. For example if the value is 20 then the result of lesser than 20 should contain 3 sets of rows

14 + 5

5 + 3

14 + 3

Answer

Here’s a recursive CTE solution (requiring MySQL 8.0+) for finding all combinations of sums of rows that add up to less than a given value. If you don’t have MySQL 8, you will probably need to write a stored procedure to do the same looping.

WITH RECURSIVE cte AS (
  SELECT duration,
         duration AS total_duration,
         CAST(duration AS CHAR(100)) AS duration_list 
  FROM test
  WHERE duration < 20
  UNION ALL
  SELECT test.duration,
         test.duration + cte.total_duration, 
         CONCAT(cte.duration_list, ' + ', test.duration)
  FROM test
  JOIN cte ON test.duration > cte.duration AND
              test.duration + cte.total_duration < 20)
SELECT duration_list, total_duration
FROM cte
WHERE duration_list != total_duration
ORDER BY total_duration ASC

Sample output for my demo on dbfiddle:

duration_list   total_duration
2 + 3           5
2 + 5           7
3 + 5           8
2 + 8           10
2 + 3 + 5       10
3 + 8           11
2 + 11          13
5 + 8           13
2 + 3 + 8       13
3 + 11          14
2 + 5 + 8       15
5 + 11          16
2 + 3 + 11      16
3 + 5 + 8       16
2 + 14          16
3 + 14          17
2 + 5 + 11      18
2 + 3 + 5 + 8   18
2 + 3 + 14      19
3 + 5 + 11      19
8 + 11          19
5 + 14          19