Count number of unique records for each user
I have a table (tblMessage) that contains a lot of messages:
id
Text
Sender
Reciever
SiteId
I need to make a SQL that list out how many unique persons (Sender) that has contacted each user (Reciever), where siteId = [number], ordered by number of unique contacts.
Example: If a person (reciever) has got 2 messages from Jesscia, 2 messages from Lisa and 4 Messages from Mike, this should be counted as 3 (3 unique contacts).
The final result shoud be something like this:
Reciever Contacts
--------------------------
Jimmi 20
Sarah 12
Mike 11
Andrew 8
Lisa 5
Jessica 3
Jason 0
Thanks for all help :)
id
Text
Sender
Reciever
SiteId
I need to make a SQL that list out how many unique persons (Sender) that has contacted each user (Reciever), where siteId = [number], ordered by number of unique contacts.
Example: If a person (reciever) has got 2 messages from Jesscia, 2 messages from Lisa and 4 Messages from Mike, this should be counted as 3 (3 unique contacts).
The final result shoud be something like this:
Reciever Contacts
--------------------------
Jimmi 20
Sarah 12
Mike 11
Andrew 8
Lisa 5
Jessica 3
Jason 0
Thanks for all help :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Quite right - the problems of answering quickly!
I've also noticed that in my Group By, the word "Receiver" was correctly spelt, whereas your field name is "Reciever".
I've also noticed that in my Group By, the word "Receiver" was correctly spelt, whereas your field name is "Reciever".
ORDER BY Contacts DESC, Reciever
(i.e. in the ORDER By clause you can reference column aliases)
no points please; it's just that Phillip didn't leave much for anyone else to do