Thread: finding a maximum or minimum sum

I have a table that looks like so:
userid | amount
---------------  1   |  $500  2   |  $400  2   | $-100  2   |   $10  3   |   $10  3   |   $10

I run a select sum(amount) from payments group by userid
userid | sum
-------------- 1    | $500 2    | $310 3    |  $20

I need to modify this query so it returns the minimum, maximum and 
average sums. Is there any way I can do this?

-Michael
_________________________________________________________________    http://fastmail.ca/ - Fast Free Web Email for
Canadians

On Mon, 11 Jun 2001, Michael Richards wrote:

> I run a select sum(amount) from payments group by userid
> userid | sum
> --------------
>   1    | $500
>   2    | $310
>   3    |  $20
> 

> I need to modify this query so it returns the minimum, maximum and 
> average sums. Is there any way I can do this?
select sum(amount),min(amount),max(amount) from payments group by userid

Is that what you mean? Or you want min/max _not_ to be grouped by userid?

> On Mon, 11 Jun 2001, Michael Richards wrote:
>
>> I run a select sum(amount) from payments group by userid
>> userid | sum
>> --------------
>> 1    | $500
>> 2    | $310
>> 3    |  $20
>>
>
>> I need to modify this query so it returns the minimum, maximum
>> and average sums. Is there any way I can do this?
> select sum(amount),min(amount),max(amount) from payments group by
> userid
>
> Is that what you mean? Or you want min/max _not_ to be grouped by
> userid?

I wanted
Max $500
Min $20
Avg $276

The trouble of course is that these values are already operated upon 
by sum in the group.

-Michael
_________________________________________________________________    http://fastmail.ca/ - Fast Free Web Email for
Canadians

"Michael Richards" <michael@fastmail.ca> writes:
> I run a select sum(amount) from payments group by userid
> I need to modify this query so it returns the minimum, maximum and 
> average sums. Is there any way I can do this?

You need two levels of grouping/aggregating to make that happen.
In 7.1 you can do it directly:

select min(amtsum), max(amtsum), avg(amtsum)
from (select sum(amount) as amtsum from payments group by userid) ss;

In prior versions you'd need to do the initial select into a temp
table and then select min/max/avg from that.
        regards, tom lane

Puuuurfect! This is exactly what I needed. Didn't know postgres 
supported subselects like that. Thanks.

-Michael

> select min(amtsum), max(amtsum), avg(amtsum)
> from (select sum(amount) as amtsum from payments group by userid)
> ss;
>
> In prior versions you'd need to do the initial select into a temp
> table and then select min/max/avg from that.

_________________________________________________________________    http://fastmail.ca/ - Fast Free Web Email for
Canadians