Re: how to select rows for a sum function

On 05/22/11 03:57, e-letter wrote:
> Readers,
>
> For a table:
>
> value name
> 10 text1
> 20 text2
> 30 text3
> 40 text4
>
> The function SELECT ... WHERE cannot be used with an aggregate
> function SUM. Is it possible to obtain a result of the rows where the
> SUM<=number? Ideally, to obtain rows where the sum of values is
> between an upper and lower value, e.g. if the target range is>=50 and
> <=60, the result would be:
>
> text1
> text2
> text3
>
> or
>
> text2
> text4
>
> Each result should go into a separate table
>




No, this is not possible unless you write a function to generate all the
possible combinations.  You are essentially asking for all possible of
these four records to be compared.  For example:
10 text1
20 text2
30 text3
40 text4
30 text1 text2
40 text1 text3
50 text1 text4
50 text2 text3
60 text2 text4
70 text3 text4
60 text1 text2 text3
80 text1 text2 text4
80 text1 text3 text4
90 text2 text3 text4
100 text1 text2 text3 text4

In general, there are 2^n - 1 combinations to be examined!

http://en.wikipedia.org/wiki/Combination#Number_of_k-combinations_for_all_k

As you can see from list of combinations; your expected result set is
incomplete; since (text1, text4) is also between 50 and 60.