Re: Aggregate within in aggregate?
Why not use a temp table to hold the aggregated values of the first SUM()
then choose the MAX in the second select statement ???
something like...
SELECT contract, grade, SUM(amount)
FROM table
GROUP BY 1, 2
INTO TEMP temp_table;
SELECT contract, grade, MAX(amount) FROM temp_table;
Matt Brickley wrote in message <3670316F.72439B17@infoave.net>...
>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.
>
>Thanks in advance
>Matt Brickley
>