SQL – Counting from distinct pairs

I have a table representing sending msgs between friends on FB:

    Sender    Receiver   Msgs #

     Dave       John        6     
     John       Dave        1    
     Sam        Dave        2   
     Mark       Ash         1    
     Ash        Dave        3 
     Mark       Steve       8     

What is an SQL query to show how many have 1,2,3… friends:

    Num of friends       Quantity
          1                 3
          2                 2
          3                 1

I have tried using a loop, but it just gets too complicated…

Answer

You could use a couple of sub-queries to achieve this by following an approach like this:

1. List of all unique senders and receivers.
2. Count of the number of friends per unique sender.
3. Group the number of friends and count how many occurrences there are.

Here’s some code that shows it working (obviously field names need to change to match your actual table):

CREATE TABLE msgs (Sender VARCHAR(20), Receiver VARCHAR(20), Msgs INT);

INSERT INTO msgs VALUES ('Dave', 'John', 6), ('John', 'Dave', 1),('Sam', 'Dave', 2),('Mark', 'Ash', 1),   ('Ash', 'Dave', 3),('Mark', 'Steve', 8);

SELECT  FriendCount AS `Num of friends`,
        COUNT(*) AS Quantity
  FROM  (
        SELECT  Sender,
                COUNT(*) AS FriendCount
          FROM  (
                SELECT DISTINCT
                        Sender,
                        Receiver
                  FROM  msgs
                UNION
                SELECT DISTINCT
                        Receiver,
                        Sender
                  FROM  msgs
                ) a
          GROUP BY Sender
        ) a
  GROUP BY FriendCount
  ORDER BY FriendCount;

DROP TABLE msgs;

Output:

Num of friends   Quantity
1                3
2                2
3                1