[thelist] SQL to get combo that sums to amount

Chris Johnston fuzzylizard at gmail.com
Wed Sep 8 22:49:08 CDT 2004


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?
> 

To clarify: you want to create a query that would go through the table
and pick out that records
1, 5, 6, & 7 quantities add to <= 50 and 
1, 5, 6, & 9 add to <= 50 and 
2, 5, & 6 add to <= 50
and so on?

Or are just trying to find any records where the quantity is <= 50? I
have the feeling that you are wanting the first one. If so, I am not
sure if this is possible to do. In addition, if records 1 + 5 + 6 + 7
are <= 50 then so are records 1 and 5 and 6 and 7 and 1 and so are 1 +
5 and 1 + 6 and 1 + 7 and 1 + 5 + 6 and 1 + 5 + 7 and 5 + 6 and 5 + 7
and 6 + 7 and 1 + 6 + 7 and so on. Over the course of several hundred
records, this could lead to a HUGE result set.

Exactly what are trying to do here and what is the logic behind it?

-- 
chris johnston

www.fuzzylizard.com

"For millions of years, mankind lived just like the animals and
something happened which unleashed the power of our imagination, we
learned to talk."
Pink Floyd


More information about the thelist mailing list