title: MySQL dynamically display distinct values from a column with count of occurrences?
body: <p>Using MySQL 5.5.34 on MAMP.</p>
  
  <p>How do I display distinct values with count of occurrences ?</p>
  
  <p><code>my_table</code> looks like this:</p>
  
  <pre><code>id fruit
  01 apple
  02 orange
  03 grape
  04 apple
  05 banana
  06 orange
  </code></pre>
  
  <p>What i'm trying to display is something like: </p>
  
  <pre><code>apple 2
  banana 1
  grape 1
  orange 2
  fruits 6
  </code></pre>
  
  <p>I could hard code the values and use count with distinct but I'm sure there is a dynamic way. I've found some examples on here using <code>group by</code> and <code>with rollup</code>, but I can't seem to get the syntax right or find a example.</p>
  
  <p>The non-dynamic way I'm doing right now:</p>
  
  <pre><code>SELECT fruit,COUNT(*) as count
  FROM my_table
  GROUP BY fruit
  ORDER BY fruit ASC
  WITH ROLLUP;
  </code></pre>
  
  <p>I hope somebody has some a clear example. I've been trying for many hours now. thanks!</p>