[NOVICE] apply limit to sum function

#1

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.

--
Sent via pgsql-novice mailing list (pgsql-novice (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

#2

On 04/27/2012 12:27 PM, e-letter wrote:

Quote:

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.

Yes. Um, I think. I am trying to decipher exactly what you want. If you

just want categories that sum to more than some amount then you can use
"having":

select somelabel, sum(somevalue) from sometable group by somelabel
having sum(somevalue) > yourtargetvalue;

If you want all the original records that contribute to a sum of more
than some value, you can use the above as the basis on which to select
records having that label. There are numerous variants of this.

One way is to use the above as a subselect. Another is with common table
expressions:

with labelcount as
(select somelabel, sum(somevalue) as groupsum from sometable group
by somelabel)
select * from
sometable
join labelcount on sometable.somelabel=labelcount.somelabel
where labelcount.groupsum > yourtargetvalue;

Yet another uses windowing functions:

select * from (
select *, sum(somevalue) over (partition by somelabel) as labelcount
from sometable) as foo
where labelcount > yourtargetvalue;

Cheers,
Steve

--
Sent via pgsql-novice mailing list (pgsql-novice (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

#3

On 27/04/2012, Steve Crawford <scrawford (AT) pinpointresearch (DOT) com> wrote:

Quote:

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.

--
Sent via pgsql-novice mailing list (pgsql-novice (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

#4

Hi

On 28 April 2012 09:26, e-letter <inpost (AT) gmail (DOT) com> wrote:

Quote:

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>

--
Sent via pgsql-novice mailing list (pgsql-novice (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

#5

On 29 April 2012 17:33, Michael Wood <esiotrot (AT) gmail (DOT) com> wrote:

Quote:

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.

Quote:

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>

--
Sent via pgsql-novice mailing list (pgsql-novice (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

#6

On 29/04/2012, Michael Wood <esiotrot (AT) gmail (DOT) com> wrote:

Quote:

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?

Ideally, yes but that would make solution only via the "knapsack"
combination algorithm type of method?

Quote:

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?

Would like to be able to choose 'text1' and 'text2' on the basis of
the sum of the values for the tuples being to the target value.

Quote:

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?

Yes, that would be acceptable: read values in order in the table until
the sum reaches a target value, then return these rows.

--
Sent via pgsql-novice mailing list (pgsql-novice (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

[NOVICE] apply limit to sum function

mailing.database.pgsql-novice mailing.database.pgsql-novice

[NOVICE] apply limit to sum function - 04-27-2012 , 02:27 PM

Re: [NOVICE] apply limit to sum function - 04-27-2012 , 02:59 PM

Re: [NOVICE] apply limit to sum function - 04-28-2012 , 02:26 AM

Re: [NOVICE] apply limit to sum function - 04-29-2012 , 10:33 AM

Re: [NOVICE] apply limit to sum function - 04-29-2012 , 10:35 AM

Re: [NOVICE] apply limit to sum function - 04-29-2012 , 02:39 PM