MySql Sql MAX and SUM error

Question

select sum(value) as 'Value',max(value)
from table_name where sum(value)=max(sum(value)) group by id_name;

The error is: Invalid use of group function (ErrorNr. 1111)

Any idea?

Thanks.

Answer 1

The =max(sum(value)) part requires comparing the results of two grouped selects, not just one. (The max of the sum.)

Let's step back, though: What information are you actually trying to get? Because the sum of the values in the table is unique; there is no minimum or maximum (or, depending on your viewpoint, there is -- the value is its own minimum and maximum). You'd need to apply some further criteria in there for the results to be meaningful, and in doing so you'd probably need to be doing a join or a subselect with some criteria.

Answer 2

Can you maybe try

SELECT Value, MXValue
FROM (
       select sum(value) as 'Value',max(value)  MXValue
       from table_name 
       group by id_name
     ) as t1
order by value desc
LIMIT 0,1

From MySQL Forums :: General :: selecting MAX(SUM())

Or you could try something like

SELECT  id_name,
        Value
FROM    (
            select id_name,sum(value) as 'Value'
            from table_name
            group by id_name
        ) t
WHERE   Value = (
                    SELECT TOP 1 SUM(Value) Mx 
                    FROM table_name
                    GROUP BY id_name 
                    ORDER BY SUM(Value) DESC
                )

Or even with an Inner join

SELECT  id_name,
        Value
FROM    (
            select id_name,sum(value) as Value
            from table_name
            group by id_name
        ) t INNER JOIN
        (
            SELECT TOP 1 SUM(Value) Mx 
            FROM table_name
            GROUP BY id_name 
            ORDER BY SUM(Value) DESC
        ) m ON Value = Mx