Обсуждение: 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