CODEWITHSUNDEEP
sql
Amine
Amine

Reputation: 1476

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

Upvotes: 0

Views: 114

Answers (3)

JC Ahn
JC Ahn

Reputation: 366

You can solve the problem with using Common Table Expressions. You should have MySQL 8.0.

See below.

WITH cte (duration) AS (
    SELECT duration 
      FROM your_table
     WHERE duration  < 20
)
SELECT a.duration + b.duration AS 'sum_of_val'
  FROM cte a JOIN cte b 
 WHERE a.duration + b.duration < 20

If you have the other version which dose not support CTE, you can use the Subquery.

See below. 

SELECT a.duration + b.duration AS 'sum_of_val'
  FROM (select duration from your_table where duration < 20 ) a
       JOIN (select duration from your_table where duration <20 ) b
 WHERE a.duration + b.duration < 20

Upvotes: 1

Nick
Nick

Reputation: 147166

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

Upvotes: 2

Parfait
Parfait

Reputation: 107652

Consider a self-join avoiding reverse duplicates with conditions that the two fields are less than zero in sum. NB: This only returns two-pair combinations.

SELECT t1.DURATION, t2.DURATION
FROM myTable t1
LEFT JOIN myTable t2
  ON t1.DURATION < t2.DURATION
WHERE t1.DURATION + t2.DURATION < 20

Upvotes: 3

Related Questions