Paulo Bueno - 1 year ago 85

MySQL Question

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,

Recommended for you: Get network issues from **WhatsUp Gold**. **Not end users.**

Answer Source

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.