MySQL subtract two count columns

Question

I have a table like this:

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

etc.

I would like output like this:

client    diff   id
------    ----   ---
 123      2      abc
 456      1      abc
 789      0      def

where diff is the count of typeA:success messages - typeB:success messages. I can get the count of the typeA success using something like:

select client, count(*) from mytable
where msg_type="typeA" and msg_body="success"

However, I can't figure out how to put another count in there (for typeB) and also subtract. I tried something like:

select client, count(*) from mytable
where msg_type="typeA" and msg_body="success" - count(*)
from mytable where msg_type="typeB" and msg_body="success"

But of course it didn't work, or I wouldn't be asking here. :) Any advice?

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.

Edit #2: I tried wrapping the SELECT query with:

select id, count(*) from (select ...) as anothertable where count_a_minus_count_b = 0;

I was hoping the output would be like:

id    count
---   -----
abc   2
def   1

where count is the number of clients where the difference between typeA:success and typeB:success is 0.

Answer 1

COUNT counts non-null values, so you can construct an expression that's non-null when msg_type = 'typeA', and an expression that's non-null when msg_type = 'typeB'. For example:

SELECT client,
       COUNT(CASE WHEN msg_type = 'typeA' THEN 1 END) AS count_a,
       COUNT(CASE WHEN msg_type = 'typeB' THEN 1 END) AS count_b,
       COUNT(CASE WHEN msg_type = 'typeA' THEN 1 END)
       - COUNT(CASE WHEN msg_type = 'typeB' THEN 1 END) AS count_a_minus_count_b
  FROM mytable
 WHERE msg_body = 'success'
 GROUP
    BY client
;

(Disclaimer: not tested.)