MySql Sql MAX and SUM error
select sum(value) as 'Value',max(value)
from table_name where s开发者_如何学Goum(value)=max(sum(value)) group by id_name;
The error is: Invalid use of group function (ErrorNr. 1111)
Any idea?
Thanks.
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
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.
精彩评论