title: MySQL sum case value equals max value of that group
body: <p>I'm trying to solve this query to return the total count of items in a group and <strong>the total of items in that group which value equals the max value of one field of that group</strong> and also which is the max(value).</p>
  <p>So far:</p>
  <pre><code>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
  </code></pre>
  <p>So far MySQL returns</p>
  <blockquote>
  <p>&quot;Invalid use of group function.&quot;</p>
  </blockquote>
  <p>Is it too complex to solve in MySQL only? Better to use algorithm?</p>