title: MySQL subtract two count columns
body: <p>I have a table like this:</p>
  
  <pre><code>client    msg_type   msg_body  id
  ------    --------   --------  ---
  123       typeA      success   abc
  123       typeB      success   abc
  456       typeA      success   abc
  456       typeB      failure   abc
  123       typeA      success   abc
  123       typeA      success   abc
  789       typeA      success   def
  789       typeB      success   def
  </code></pre>
  
  <p>etc.</p>
  
  <p>I would like output like this:</p>
  
  <pre><code>client    diff   id
  ------    ----   ---
   123      2      abc
   456      1      abc
   789      0      def
  </code></pre>
  
  <p>where <code>diff</code> is the count of <code>typeA:success</code> messages - <code>typeB:success</code> messages.  I can get the count of the typeA success using something like:</p>
  
  <pre><code>select client, count(*) from mytable
  where msg_type="typeA" and msg_body="success"
  </code></pre>
  
  <p>However, I can't figure out how to put another count in there (for typeB) and also subtract.
  I tried something like:</p>
  
  <pre><code>select client, count(*) from mytable
  where msg_type="typeA" and msg_body="success" - count(*)
  from mytable where msg_type="typeB" and msg_body="success"
  </code></pre>
  
  <p>But of course it didn't work, or I wouldn't be asking here.  :)  Any advice?</p>
  
  <p>Edit:  added another column.  I tried the two suggestions given, but it only seems to return the results for one of the ids, not both.</p>
  
  <p>Edit #2: I tried wrapping the SELECT query with:</p>
  
  <pre><code>select id, count(*) from (select ...) as anothertable where count_a_minus_count_b = 0;
  </code></pre>
  
  <p>I was hoping the output would be like:</p>
  
  <pre><code>id    count
  ---   -----
  abc   2
  def   1
  </code></pre>
  
  <p>where count is the number of clients where the difference between typeA:success and typeB:success is 0.</p>