title: select distinct substring values of a field and count the number of instances of a char in that selection
body: <p>I'm trying to select distinct substring values of a field and count the number of instances of a char in that selection.
  I've found <a href="https://stackoverflow.com/questions/5427467/mysql-count-instances-of-substring-then-order-by">this</a> wonderful post which answers half of it.</p>
  
  <p>So, so far, i can count the instances of a char in my field, it works great. Now the even harder part, what if i select a piece of string using :</p>
  
  <pre><code>SELECT DISTINCT SUBSTRING_INDEX(my_field, '-', -1) AS chunk
  </code></pre>
  
  <p>In this case i'm only selecting the last part of the string (everything after the last'-'). How can i apply this formula to chunk (trying to count the number of instances of '_' in the new string ? :</p>
  
  <pre><code>(LENGTH(chunk) - LENGTH(REPLACE(chunk, '_', ''))) / LENGTH('_')
  </code></pre>
  
  <p>I know i shoud be using HAVING to make operation on chunk as it's not a real field, but how can i do something like :</p>
  
  <pre><code>SELECT DISTINCT SUBSTRING_INDEX(my_field, '-', -1) AS chunk, (LENGTH(chunk) - LENGTH(REPLACE(chunk, '_', ''))) / LENGTH('_') AS total FROM my_field HAVING total &lt; 2 
  </code></pre>
  
  <p>The problem here is that i can't use 'chunk' in the last part since it's not a field..</p>