Re: SQL: Aggregate within in aggregate?

Matt Brickley wrote: > > In Informix SE 7.23, 4GL 6.04 > How can I in effect nest an aggreate like SELECT MAX(SUM(column)) > where.....? > If I have a table > > contract char(3) > grade char (1) > amount integer > > For a given contact I want to select the grade having the MAX of > SUM(amount) > So if my data is: > contract grade amount > XYZ A 20 > XYZ B 20 > XYZ B 19 > XYZ A 21 > XYZ C 30 > > I want the SQL statement to return me the grade value of "A" since the > SUM(amount) of all A grade rows is 41 and the SUM(amount) of all B grade > rows is 39 and C = 30 > Please help me with the syntax. You can't do nested aggregates, so you have to unbundle the query using a temp table to store the intermediate results. SELECT Contract, Grade, SUM(Amount) AS Sum_Amount FROM A_Table GROUP BY Contract, Grade INTO TEMP Contract_Summed; That's easy. Now you want the Contract and Grade for each Contract where the Sum_Amount is greatest. In case of ties between grades for a single contract, I assume multiple rows should be returned. This isn't so easy... SELECT Contact, Grade, Sum_Amount FROM Contract_Summed CS1 WHERE CS1.Sum_Amount = (SELECT MAX(CS2.Sum_Amount) FROM Contract_Summed CS2 WHERE CS2.Contract = CS1.Contract) A correlated sub-query? Hmm...I think it will work, but I've not tested it, and there might be a simpler solution. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>