How to cross group by two columns in SQL?

Question

I have a personal messaging system on my website, which is pretty simple. But I would like to have an admin page where all conversations are shown between users and their number of messages.

So the table looks (simplified version):

CREATE TABLE pm ( id INT(10) NOT NULL AUTO_INCREMENT, from INT(10) NOT NULL REFERENCES (usertable), to INT(10) NOT NULL REFERENCES (usertable), message BLOB NOT NULL ); 

Example:

Let's say I have some users: Mark, John, Brian and Kate.

The mark (from) sends 5 messages to John (to), and John (from) sends 3 messages to Mark (to).

Kate (from) sends 2 messages to Bryan (to), and Bryan (from) sends 1 message to Kate (to).

I need a result set that shows

Mark - John - 8 posts

Kate - Brian - 3 posts

And this is for all users in my table right away.

I was really stuck with this and I searched everywhere but couldn't find a solution. The difficulty is that I want all users to be listed, and I need to somehow cross the "from" and "to" columns ...

I hope someone can help. Thanks in advance.

Answer 1

 select from_id, to_id, count(*) count_between from ( select from_id, to_id from pm union all select to_id, from_id from pm ) combined where from_id < to_id group by from_id, to_id 

Full sample

 CREATE TABLE pm (from_id int,to_id int); insert pm select 1,2; insert pm select 1,2; insert pm select 1,2; insert pm select 1,2; insert pm select 1,2; insert pm select 2,1; insert pm select 2,1; insert pm select 2,1; insert pm select 3,4; insert pm select 3,4; insert pm select 4,3; select from_id, to_id, count(*) count_between from ( select from_id, to_id from pm union all select to_id, from_id from pm ) combined where from_id < to_id group by from_id, to_id --- results from_id to_id count_between ----------- ----------- ------------- 1 2 8 3 4 3 

To include identifiers in names, use the regular user table or some of them. eg.

 select u1.name from_, u2.name to_, count(*) count_between from ( select from_id, to_id from pm union all select to_id, from_id from pm ) combined join users u1 on u1.id = combined.from_id join users u2 on u2.id = combined.to_id where from_id < to_id group by u1.name, u2.name 

Answer 2

You can make a unique conversation by first specifying the person with the largest identifier. Then you can group by identifier of both persons:

 select ut1.name , ut2.name , convo_pairs.message_count from ( select case when [from] < [to] then [to] else [from] end as p1 , case when [from] < [to] then [from] else [to] end as p2 , count(*) as message_count from pm group by case when [from] < [to] then [to] else [from] end as p1 , case when [from] < [to] then [from] else [to] end as p2 ) as convo_pairs join usertable ut1 on ut1.id = convo_pairs.p1 join usertable ut2 on ut2.id = convo_pairs.p2 

Answer 3

try it

 select ISNULL(a.from,b.from) + '-' + ISNULL(a.to,b.to) + '-' + convert(varchar(a.count+b.count)) + 'messages' (select pm1.from,pm1.to,count(1) count from pm pm1 group by pm1.from,pm1.to) a FULL OUTER (select pm1.from,pm1.to,count(1) count from pm pm1 group by pm1.from,pm1.to) b on a.from=b.to and a.to=b.from