title: SQL query to count in group by with specific condition
body: <p>My table data:</p>
  
  <pre><code>id      fieldId  Name   Text
  ----    ------  -----   ----
  1        101    name1   a1
  2        102    name2   a2
  3        101    name1   a1
  4        103    name3   a2
  5        102    name2   a3
  6        101    name1   c1
  7        101    name1   a3
  8        102    name2   
  9        101    name1   b2
  10       103    name3   c1
  </code></pre>
  
  <p>I need output like </p>
  
  <pre><code>Name    Count(fieldId)  Count_id_text_is_either_a1_or_a2_a3
  ------      ------        -------
  name1   4                   2
  name2   3                   2
  name3   2                   1
  </code></pre>
  
  <ul>
  <li><code>Name</code> = I need to group by fieldId. </li>
  <li><code>Count(fieldId)</code> = Count each group records.</li>
  <li><code>Count_id_text_is_either_a1_or_a2_a3</code> = This is the column I want the count of records of group only if <code>Text like 'a1-%' or 'a2-%' or 'a3-%'</code>.</li>
  </ul>
  
  <p>Can this be done by single query? If yes the please explain how? If no then what is the most efficient way to do this? </p>