title: Is there a simpler way to find MODE(S) of some values in MySQL
body: <p>MODE is the value that occurs the MOST times in the data, there can be ONE MODE or MANY MODES</p>
  
  <p>here's some values in two tables (<a href="http://sqlfiddle.com/#!2/1f727/5/1" rel="nofollow">sqlFiddle</a>)</p>
  
  <pre><code>create table t100(id int auto_increment primary key, value int);
  create table t200(id int auto_increment primary key, value int);
  
  insert into t100(value) values (1),
                                 (2),(2),(2),
                                 (3),(3),
                                 (4);
  insert into t200(value) values (1),
                                 (2),(2),(2),
                                 (3),(3),
                                 (4),(4),(4);
  </code></pre>
  
  <p>right now, to get the MODE(S) returned as comma separated list, I run the below query for table <code>t100</code></p>
  
  <pre><code>     SELECT GROUP_CONCAT(value) as modes,occurs
       FROM
          (SELECT value,occurs FROM 
             (SELECT value,count(*) as occurs
              FROM
              T100
              GROUP BY value)T1,
          (SELECT max(occurs) as maxoccurs FROM 
              (SELECT value,count(*) as occurs
               FROM
               T100
               GROUP BY value)T2
          )T3
          WHERE T1.occurs = T3.maxoccurs)T4
        GROUP BY occurs;
  </code></pre>
  
  <p>and the below query for table <code>t200</code> (same query just with table name changed) I have 2 tables in this example because to show that it works for cases where there's 1 MODE and where there are multiple MODES.</p>
  
  <pre><code>     SELECT GROUP_CONCAT(value) as modes,occurs
       FROM
          (SELECT value,occurs FROM 
             (SELECT value,count(*) as occurs
              FROM
              T200
              GROUP BY value)T1,
          (SELECT max(occurs) as maxoccurs FROM 
              (SELECT value,count(*) as occurs
               FROM
               T200
               GROUP BY value)T2
          )T3
          WHERE T1.occurs = T3.maxoccurs)T4
        GROUP BY occurs;
  </code></pre>
  
  <p>My question is "Is there a simpler way?" </p>
  
  <p>I was thinking like using <code>HAVING count(*) = max(count(*))</code> or something similar to get rid of the extra join but couldn't get <code>HAVING</code> to return the result i wanted.</p>
  
  <p>UPDATED:
  as suggested by @zneak, I can simplify <code>T3</code> like below:</p>
  
  <pre><code>     SELECT GROUP_CONCAT(value) as modes,occurs
       FROM
          (SELECT value,occurs FROM 
             (SELECT value,count(*) as occurs
              FROM
              T200
              GROUP BY value)T1,
          (SELECT count(*) as maxoccurs
               FROM
               T200
               GROUP BY value
               ORDER BY count(*) DESC
               LIMIT 1
          )T3
          WHERE T1.occurs = T3.maxoccurs)T4
        GROUP BY occurs;
  </code></pre>
  
  <p>Now is there a way to get ride of T3 altogether?
  I tried this but it returns no rows for some reason</p>
  
  <pre><code>  SELECT value,occurs FROM  
      (SELECT value,count(*) as occurs
       FROM t200
       GROUP BY `value`)T1
    HAVING occurs=max(occurs)  
  </code></pre>
  
  <p>basically I am wondering if there's a way to do it such that I only need to specify <code>t100</code> or <code>t200</code> once.</p>
  
  <p>UPDATED: i found a way to specify <code>t100</code> or <code>t200</code> only once by adding a variable to set my own maxoccurs like below</p>
  
  <pre><code>  SELECT GROUP_CONCAT(CASE WHEN occurs=@maxoccurs THEN value ELSE NULL END) as modes 
    FROM 
      (SELECT value,occurs,@maxoccurs:=GREATEST(@maxoccurs,occurs) as maxoccurs
       FROM (SELECT value,count(*) as occurs
             FROM t200
             GROUP BY `value`)T1,(SELECT @maxoccurs:=0)mo
       )T2
  </code></pre>