CODEWITHSUNDEEP
sqlsql-server
JustLooking
JustLooking

Reputation: 2486

SQL that returns all the permutations of a summed column

Shot in the dark here. I'd personally struggle to come up with a simple SQL statement to do the following (if it can even be done), so I thought I'd throw this out here:

Let's say we have the following data:

ID VALUE
-- -----
1  60
2  60
3  60
4  60

And I wanted to find all the permutations of records that SUM to 120. Meaning, the results would be 6 rows:

1 AND 2
1 AND 3
1 AND 4
--2 AND 1 (already used)
2 AND 3
2 AND 4
--3 AND 1 (already used)
--3 AND 2 (already used)
3 AND 4

They actually want a "random sampling" of that result-set, but I need to know if I can even get that result-set. Of course, the real data wouldn't be that easy (everything 60), and the question was posed as "10 records that add up to 5 minutes" (the field is a duration field), which leads to other questions on how to handle that, but let me see if I can start with just getting permutations before actually getting more sophisticated.

Thanks.

Upvotes: 0

Views: 107

Answers (3)

Amatya Annamaneni
Amatya Annamaneni

Reputation: 27

Table Tvalues

ID VALUE
-- -----
1  60
2  60
3  60
4  60

Select A.ID, B.ID from TValues A
join TValues B on B.ID != A.ID
where
    (A.Value+B.Value) = 120
  and
    A.ID < B.ID  -- eliminates dups, if (1,3) is printed, (3,1 will not be printed)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

These are combinations, not permutations. If you want all 2-way combinations, then use a self-join:

select t1.*, t2.*
from t t1 join
     t t2
     on t1.id < t2.id and
        t1.value + t2.value = 60;

For an about 10% random sample, you can use:

select t1.*, t2.*
from t t1 join
     t t2
     on t1.id < t2.id and
        t1.value + t2.value = 60
where rand() < 0.1;

Upvotes: 1

SqlZim
SqlZim

Reputation: 38063

select l.id, r.id, l.value+r.value as sum
from t l
  inner join t r
    on l.id < r.id
where l.value+r.value = 120
order by l.id, r.id

rextester demo: http://rextester.com/FWCLT49699

returns:

+----+----+-----+
| id | id | sum |
+----+----+-----+
|  1 |  2 | 120 |
|  1 |  3 | 120 |
|  1 |  4 | 120 |
|  2 |  3 | 120 |
|  2 |  4 | 120 |
|  3 |  4 | 120 |
+----+----+-----+

Upvotes: 1

Related Questions