Re: apply limit to sum function

On 29 April 2012 17:33, Michael Wood <esiotrot(at)gmail(dot)com> wrote:
> 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

Sorry, I forgot: (text2, 12), (text3, 23), but obviously that's also
over the limit.

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