[thelist] SQL to get combo that sums to amount

Ken Schaefer ken.schaefer at gmail.com
Wed Sep 8 22:46:53 CDT 2004


With no other information than what you've provided, I think you'd
need to create sets of every possible combination of records, and then
test the summed total. You can't do that with straight SQL alone. Eg
you'd need to test:

Records: 1, 2, 3, 4...n
Records: 1+2, 1+3, 1+n, 2+3, 2+4, 2+n ... (n-1) + n
Records: 1+2+3, 1+2+4, 1+2+5...(n-2)+(n-1)+n
...
Records: 1+2+3+...+n

SQL works on set theory - you assemble sets, and discard certain
things by using criteria - but the criteria needs to apply to a set of
1 (or more) records. You don't seem to have any criteria for
assembling the sets - you just want every possible arbitrary set,
which isn't possible to assemble in straight SQL (well, maybe
possible, but it would be very, very, convoluted)

Cheers
Ken


On Wed, 08 Sep 2004 20:20:44 -0700 (PDT), aspscript at canada.com
<aspscript at canada.com> wrote:
> I have a table where each record has a field called
> "quantity" with an integer value.  How would I
> construct a query that searches thru hundreds of
> records and determines what combination of those
> records has a sum of the quantities that equals 50?
> 
> Any help or clues as to how to get started appreciated.


More information about the thelist mailing list