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.

Re: "I tried the two suggestions given, but it only seems to return the results for one of the ids, not both": All of the answers showed how to compute the difference between the counts; and I think they were all pretty clear about it. Rather than simply trying suggestions without reading them, I recommend you try to learn from them, and ask questions if there are parts you don't understand. :-/ — ruakh, Aug 20 '13 at 22:57
@ruakh I thought I understood the queries I was running, hence why I was trying different things. I think I got it with this: select id, count(*) from (SELECT id, client, 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) as sometable where count_a_minus_count_b < 1 group by id; — user2406467, Aug 20 '13 at 23:07
related for sum http://stackoverflow.com/questions/826365/how-do-i-add-two-count-results-together — Ciro Santilli OurBigBook.com, May 20 '16 at 20:49

ruakh · Answer 1 · 2013-08-20T22:54:54.563

5

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

edited Aug 20 '13 at 22:54

answered Aug 20 '13 at 22:26

ruakh

175,680
26
273
307

*"(Disclaimer: not tested.)"* - Worked fine for me. I was looking for something like this and helped me a lot. *Cheers* – Funk Forty Niner Apr 20 '16 at 01:45
@Fred-ii-: Glad to hear it! :-) – ruakh Apr 20 '16 at 04:07
Thanks :-) I'm not an MySQL guru but I have my moments. Yet, I know good syntax when I see it and what I had in mind which was a CASE but didn't know how to use it for what I wanted to accomplish. I had to do a slight modification but it worked out beautifully in the end. *Cheers* – Funk Forty Niner Apr 20 '16 at 12:25

score 2 · Answer 2 · answered Aug 20 '13 at 23:22

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 at SQL-Fiddle

score 0 · Answer 3 · answered Aug 20 '13 at 22:26

0

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

answered Aug 20 '13 at 22:26

Ike Walker

64,401
14
110
109

spencer7593 · Answer 4 · 2013-08-20T23:15:22.423

Here's 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 specific; for a more portable query, use a less concise CASE expression to obtain an equivalent result.)

As more terse and obfuscated 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

MySQL subtract two count columns

4 Answers4