MySQL subtract two count columns
I have a table like this:
client msg_type msg_body
------ -------- --------
123 typeA success
123 typeB success
456 typeA success
456 typeB failure
123 typeA success
123 typeA success
etc.
I would like output like this:
client diff
------ ----
123 2
456 1
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?
No comments:
Post a Comment