October 13, 2007 at 3:45 pm
Hi guys,
I have a table with fields "To User", "From User" and others (which I'm not going to use at this time).
Basically this is a messages table that stores when messages have come from some particular user to another particular user, and it allows reversals.
So data may look like
From User To User
user1 user2
user2 user1
user1 user2
user1 user3
user3 user1
As you can see it allows duplication (theres no primary key) simply because people can send multiple messages to one another.
I'm trying to write a query that will list all of the possible pairs of members (but combines the reversals!!!) and the number of messages they have sent to each other.
I wrote a simple query:
SELECT [From User], [To User], Count(*) AS "Count"
FROM Messages
GROUP BY [From User], [To User]
ORDER BY "Count"
And this query works, but of course it is returning unique results, so where you have
"From User" user1 "To User" user2, it will list separately from the opposite which is "From User" user2 "To User" user1.
But yeah, anyone know if there is a way to combine those results and hence show the TOTAL number of messages passed between user1 and user2, user1 and user3, etc (all the possible pairings)?
I hope my description wasn't too confusing, its a relatively simple idea.
As always, any help is appreciated.
October 13, 2007 at 4:17 pm
Sure...
[font="Courier New"]--===== This is just to build some sample data and is NOT part of the solution
DECLARE @yourtable TABLE (FromUser VARCHAR(10), ToUser VARCHAR(10))
 INSERT INTO @yourtable (FromUser, ToUser)
 SELECT 'user1','user2' UNION ALL
 SELECT 'user2','user1' UNION ALL
 SELECT 'user1','user2' UNION ALL
 SELECT 'user1','user3' UNION ALL
 SELECT 'user3','user1'
--===== This is the solution
 SELECT d.FromUser, d.ToUser, COUNT(*) AS TimeConnected
   FROM (--==== Derived table swaps users when needed
         SELECT FromUser, ToUser
           FROM @yourtable
          WHERE FromUser < ToUser
          UNION ALL
         SELECT ToUser AS FromUser,
                FromUser AS ToUser
           FROM @yourtable
          WHERE ToUser <= FromUser
        ) d
  GROUP BY d.FromUser, d.ToUser[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
October 13, 2007 at 4:26 pm
Awesome, that works as far as I can tell, thank you very much Jeff!
October 13, 2007 at 4:49 pm
Here's an extended problem (which I was unable to solve and not sure if it can be at all).
So Jeff's code works, but another field that I have in the Messages table is "Time Sent" which obviously records when the message was sent.
I want to be able to group by month the total transactions between any two pairs of users. This sounds difficult and note sure if it can be done with the way I structured my table and how Jeff wrote the previous query, but none the less if anyone has any ideas let me know.
Results of it should look something like
From User To User Total Messages Month
user1 user2 5 January
user1 user2 10 February
user1 user3 15 February
and so on.
October 13, 2007 at 8:22 pm
Heh... you just need to look it in the eye... 😉
[font="Courier New"]
--===== This is just to build some sample data and is NOT part of the solution
DECLARE @yourtable TABLE (FromUser VARCHAR(10), ToUser VARCHAR(10), Date DATETIME)
 INSERT INTO @yourtable (FromUser, ToUser, Date)
 SELECT 'user1','user2', '20070115' UNION ALL
 SELECT 'user2','user1', '20070116' UNION ALL
 SELECT 'user1','user2', '20070117' UNION ALL
 SELECT 'user1','user3', '20070118' UNION ALL
 SELECT 'user3','user1', '20070118' UNION ALL
 SELECT 'user1','user2', '20070201' UNION ALL
 SELECT 'user2','user1', '20070205' UNION ALL
 SELECT 'user1','user2', '20070301' UNION ALL
 SELECT 'user1','user3', '20070310' UNION ALL
 SELECT 'user3','user1', '20070311'
--===== This is the solution
 SELECT d.FromUser, d.ToUser, d.YrMon, COUNT(*) AS TimesConnected
   FROM (--==== Derived table swaps users when needed
         SELECT FromUser, 
                ToUser,
                REPLACE(CONVERT(CHAR(7),Date,102),'.','-') AS YrMon
           FROM @yourtable
          WHERE FromUser < ToUser
          UNION ALL
         SELECT ToUser AS FromUser,
                FromUser AS ToUser,
                REPLACE(CONVERT(CHAR(7),Date,102),'.','-') AS YrMon
           FROM @yourtable
          WHERE ToUser <= FromUser
        ) d
  GROUP BY d.FromUser, d.ToUser, d.YrMon
[/font]
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply