Aggregate function calls may not be nested?

[beth]

Hi guys,

How can I find the max from the sum of values? If I try the following I get an error:

Code: Select all

dbadmin=> SELECT c1, SUM(c2) c2_sum FROM test GROUP BY c1 ORDER BY c1, c2_sum;
 c1 | c2_sum
----+--------
 A  |      3
 B  |      4
 C  |      4
(3 rows)

Code: Select all

dbadmin=> SELECT MAX(SUM(c2)) c2_sum_max FROM test GROUP BY c1;
ERROR 2135:  Aggregate function calls may not be nested

What I really want is output from a query to be this:

Code: Select all

 c1 | c2_sum_max
----+-----------
 B  |          4
 C  |          4

Where I get a list of all C1 values that have the max sum...

Anyone know an esay query that I can use to do this?

[JimKnicely]

Hi Beth,

There are probably plenty of ways to write a query to do what you want... Here is one:

Code: Select all

dbadmin=> CREATE TABLE test (c1 varchar(100), c2 int);
CREATE TABLE
dbadmin=> insert into test values ('A', 3);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test values ('B', 4);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test values ('C', 4);
 OUTPUT
--------
      1
(1 row)

dbadmin=> commit;

Code: Select all

dbadmin=> SELECT c1, c2_sum c2_sum_max
dbadmin->   FROM (SELECT c1, SUM(c2) c2_sum FROM test GROUP BY c1 ORDER BY c1, c2_sum) foo
dbadmin->  WHERE c2_sum = (SELECT MAX(c2_sum) FROM (SELECT SUM(c2) c2_sum FROM test GROUP BY c1) foo)
dbadmin->  ORDER BY c1;
 c1 | c2_sum_max
----+------------
 B  |          4
 C  |          4
(2 rows)

[beth]

Thank you, Jim!