Re: apply limit to sum function

Hi

On 28 April 2012 09:26, e-letter <inpost(at)gmail(dot)com> wrote:
> On 27/04/2012, Steve Crawford <scrawford(at)pinpointresearch(dot)com> wrote:
>> On 04/27/2012 12:27 PM, e-letter wrote:
>>> Readers,
>>>
>>>   Is it possible to apply the 'sum' function, but limit the value and
>>>   then show the tuples of the result? For example, the table has:
>>>
>>>   text1, 10
>>>   text2, 12,
>>>   text3, 23
>>>
>>>   Instead of applying the 'sum' function to all values, the request is
>>>   to be able to select those values that result in a sum of a target
>>>   value (e.g.<  22) and then return the tuples.
>>
>> select somelabel, sum(somevalue) from sometable group by somelabel
>> having sum(somevalue) > yourtargetvalue;
> ...
>> select * from (
>> select *, sum(somevalue) over (partition by somelabel) as labelcount
>> from sometable) as foo
>> where labelcount > yourtargetvalue;
>
> So far, tried these methods above but produced:
>
> text1, 10
> text2, 12
>
> It seems that these queries produce a result that corresponds to _each
> row_ value being evaluated against the target value. The desired
> output is that the sum of the values in the result is to be evaluated
> against the target. But thanks anyway so far.

I'm not entirely clear on what you want.

e.g. do you want to generate all possible permutations of your values
and then find the sums that are less than your target? If so, how do
you decide which subset of rows to return?

i.e.:

1: (text1, 10) => 10

2: (text2, 12) => 12

3: (text3, 23) => 23

4: (text1, 10), (text2, 12) => 22

5: (text1, 10), (text3, 23) => 33

6: (text1, 10), (text2, 12), (text3, 23) => 45

Only options 1 and 2 are < 22, so I assume you're looking for either
(text1, 10) or (text2, 12) as an answer given this example data? How
do you choose which one?

Or do you just want to take the data in the order given and add the
values until the total goes above 22 and then return the rows you had
just before going over the target value?

Or something else?

--
Michael Wood <esiotrot(at)gmail(dot)com>