Skip site navigation (1)
Skip section navigation (2)
## Re: apply limit to sum function

### In response to

### pgsql-novice by date

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>

- Re: apply limit to sum function at 2012-04-29 15:33:30 from Michael Wood

Next: From:e-letterDate:2012-04-29 19:39:46Subject: Re: apply limit to sum functionPrevious: From: Michael WoodDate: 2012-04-29 15:33:30Subject: Re: apply limit to sum function