MySQL subtracts 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 

and etc.

I would like to conclude as follows:

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

where diff is the number of messages typeA:success - typeB:success . I can get a Type A success score using something like:

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

However, I cannot figure out how to add another account there (for type B), 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, this did not work, or I would not ask here. :) Any tips?

Edit: another column added. I tried the two suggested sentences, but this only returns results for one of the identifiers, not both.

Edit # 2: I tried to wrap a SELECT query with

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

I was hoping the result would be like this:

 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-zero values, so you can build an expression that is not null when msg_type = 'typeA' , and an expression that is not null when msg_type = 'typeB' . For instance:

 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 verified.)

Answer 2

Another way:

 SELECT d.client, COALESCE(a.cnt, 0) - COALESCE(b.cnt, 0) AS diff, d.id FROM ( SELECT DISTINCT client, id FROM mytable ) AS d LEFT JOIN ( SELECT client, COUNT(*) AS cnt, id FROM mytable WHERE msg_type = 'typeA' AND msg_body = 'success' GROUP BY client, id ) AS a ON d.client = a.client AND d.id = a.id LEFT JOIN ( SELECT client, COUNT(*) AS cnt, id FROM mytable WHERE msg_type = 'typeB' AND msg_body = 'success' GROUP BY client, id ) AS b ON d.client = b.client AND d.id = b.id ; 

Tested with SQL-Fiddle

Answer 3

Here you go:

 select client, (sum(case when msg_type='typeA' and msg_body='success' then 1 else 0 end) - sum(case when msg_type='typeB' and msg_body='success' then 1 else 0 end)) as diff from your_table group by client 

Answer 4

Here is one way to get the result:

 SELECT t.client , SUM(t.msg_type<=>'typeA' AND t.msg_body<=>'success') - SUM(t.msg_type<=>'typeB' AND t.msg_body<=>'success') AS diff FROM mytable t GROUP BY t.client 

(The expressions in this query are MySQL-dependent; for a more portable query, use a less compressed CASE expression to get an equivalent result.)

---

As a shorter and confusing alternative to return the same result:

 SELECT t.client , SUM((t.msg_body<=>'success')*((t.msg_type<=>'typeA')+(t.msg_type<=>'typeB')*-1)) AS diff FROM mytable t GROUP BY t.client