Difference between two COUNTS

Question

I have a mysql table private_messages (pm) that looks like this:

userid --- sender --- text

While Userid and Sender are both IDs from the table user. Now I want to do a query where a user gets the difference between the amount of pms he sent to another user and the received pms from this one - grouped by Userid So I want to put those two queries in one and get the different out of it:

SELECT count(*) AS s_count, sender FROM pm WHERE userid=".$userid" GROUP BY sender
SELECT count(*) AS u_count, userid FROM pm WHERE sender=".$userid." GROUP BY userid

and now I want the difference betweens_count and u_count on all matching Userids.

Answer 1

If you want statistics per user you've corresponded with, you can just collect all messages using UNION ALL and sum them in an outer query;

SELECT counterpart, SUM(sent) sent, SUM(received) received,
       SUM(sent) - SUM(received) difference
FROM (
  SELECT 1 sent, 0 received, userid counterpart 
  FROM messages WHERE sender = <your user id>
  UNION ALL
  SELECT 0 sent, 1 received, sender counterpart
  FROM messages WHERE userid = <your user id>
) a
GROUP BY counterpart;

An SQLfiddle to test with.

Answer 2

You would be wise to add to your select list any column(s) over which you group the results:

SELECT sender, COUNT(*) AS s_count FROM pm WHERE userid = ? GROUP BY sender
SELECT userid, COUNT(*) AS u_count FROM pm WHERE sender = ? GROUP BY userid

You will then see that the first query returns the number of messages with the given userid, broken down by sender; whereas the second query returns the number of messages with the given sender, broken down by userid. As such, it doesn't make much sense to combine these queries "and get the difference out of it".