Skip site navigation (1) Skip section navigation (2)

Re: apply limit to sum function

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: e-letter <inpost(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: apply limit to sum function
Date: 2012-04-27 19:59:31
Message-ID: 4F9AFAA3.1000800@pinpointresearch.com (view raw or flat)
Thread:
Lists: pgsql-novice
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.
>
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


In response to

Responses

pgsql-novice by date

Next:From: e-letterDate: 2012-04-28 07:26:33
Subject: Re: apply limit to sum function
Previous:From: e-letterDate: 2012-04-27 19:27:09
Subject: apply limit to sum function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group