Paulo Bueno - 1 year ago 85
MySQL Question

# MYSQL sum case value equals max value of that group

I'm trying to solve this query to return the total count of itens in a group and the total of itens in that group wich value equals the max value of one field of that group and also wich is the max(value).

So far:

``````SELECT
limid, COUNT(*), SUM(CASE WHEN cotavertical=MAX(cotavertical) THEN 1 ELSE 0 END), MAX(cotavertical)
FROM limites
LEFT JOIN tbparentchild ON parent=limid
LEFT JOIN tbspatialbi ON child=rgi
WHERE limtipo=4 AND x=1
GROUP BY limid
``````

So far mysql returns "Invalid use of group function."

Is it too complex to solve in mysql only? Better to use algorithm?

Thanks,

You are trying to use the max value for each group in an aggregate function (`SUM`) before the aggregation has finished, and hence it is not available. The query below uses the strategy of joining a subquery which contains the max value of `cotavertical` for each `limid` group. In this case, the max value per group which you want to use will now be available from another source, and you can sum using it.

``````SELECT l.limid,
COUNT(*),
SUM(CASE WHEN cotavertical = t.cotamax THEN 1 ELSE 0 END),
MAX(cotavertical)
FROM limites l
LEFT JOIN tbparentchild pc
ON pc.parent = l.limid
LEFT JOIN tbspatialbi s
ON pc.child = s.rgi
LEFT JOIN
(
SELECT limid, MAX(cotavertical) AS cotamax
FROM limites
LEFT JOIN tbparentchild
ON parent = limid
LEFT JOIN tbspatialbi
ON child = rgi
WHERE limtipo = 4 AND x = 1
GROUP BY limid
) t
ON l.limid = t.limid
WHERE limtipo = 4 AND l.x = 1
GROUP BY l.limid
``````

Another option for solving your problem would be to use a subquery directly in the `CASE` statement. But, given the size and number of joins in your original query, this would be way uglier than the query above. MySQL does not support common table expressions, which would have helped with both these solutions.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download