Search code examples
sqlsumuniquecombinationspermutation

Store Pricing Combinations and Sums w/ SQL

I have a table that looks something like

grain 10
rice  10
bread 10
wine  20
chair 20
bed   30
desk  30

and I would like to create all possible combinations with the sum of the prices, including the unique values to create something like

grain grain grain grain 40
grain grain grain rice  40
grain grain rice  rice  40
grain grain grain desk  60

what would be the quick and dirtiest way of accomplishing this?

Solution

  • Use three joins:

    select t1.col1, t2.col1, t3.col1, t4.col1, 
       (t1.price + t2.price + t3.price + t4.price)
from t t1 join
     t t2
     on t1.col1 <= t2.col1 join
     t t3
     on t2.col1 <= t3.col1 join
     t t4
     on t3.col1 <= t4.col1;