title: Mysql group by that counts have different logic
body: <p>I have a table with three fields:.</p>
  
  <pre><code>field1 - field2 - field3
  </code></pre>
  
  <blockquote>
    <p>field1 is a id;<br>
     field2 is the date;<br>
     field3 only has two possible
    values ("ac" and "re").</p>
  </blockquote>
  
  <p>I need to know if is possible to have a query with <code>group by</code> that will give me the results shown at the end:</p>
  
  <pre><code>select field2
         , count(total per date)
         , Count(where field3 = "re") 
    from myTable
   group by 1;
  </code></pre>
  
  <p>If my table is:</p>
  
  <pre><code>field1 - field2 - field3
  =========================
   item1 - date1  - "ac"
   item2 - date1  - "re"
   item3 - date1  - "re"
   item4 - date2  - "re"
   item5 - date2  - "ac"
   item6 - date3  - "ac"
  </code></pre>
  
  <p>The result will be like:</p>
  
  <pre><code> - date1 - 3 - 2
   - date2 - 2 - 1
   - date3 - 1 - 0
  </code></pre>