MAX(SUM(...

[Guest_imported]

I would like to max the sum of a field:
I have:
"SELECT SUM(qnty),prod FROM tpurcd
GROUP BY prod"
-->this return the sums

but what i want is the max of these sums
like this:
"SELECT MAX(SUM(qnty)),prod,TO_CHAR(recv,'MM') FROM tpurcd
GROUP BY prod"
but error "not a single-group group function"

any ideas???

gawell

 

[crufty]

If you want to find the max SUM for a given set of date periods:

select MAX(z0.gross), z0.prod, z0.date
from
(select SUM(qnty) gross, prod, TO_CHAR( recv, 'MM') date
from tpurcd
group by prod, TO_CHAR( recv, 'MM') ) z0
group by z0.prod, z0.date ;

 

[OraCool]

Your SQL:
"SELECT MAX(SUM(qnty)),prod,TO_CHAR(recv,'MM') FROM tpurcd
GROUP BY prod"
but error "not a single-group group function"

Should Read:
"SELECT MAX(SUM(qnty)),prod,TO_CHAR(recv,'MM') FROM tpurcd
GROUP BY prod, TO_CHAR(recv,'MM')"

 

[crufty]

Oracle 8i (do not know about 9i) does not allow nesting of aggregate functions so the correction to the SQL will still provide the same error.

 

[johannesd]

Can you try something like the following....?

SELECT MAX(a.Max1) FROM
(SELECT MAX(SUM(qnty))AS MAX1
FROM
tpurcd
UNION
SELECT MAX(prod) AS MAX1
FROM
tpurcd) a