Link to home
Create AccountLog in
Avatar of webressurs
webressursFlag for Norway

asked on

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 :)
ASKER CERTIFIED SOLUTION
Avatar of Phillip Burton
Phillip Burton

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of PortletPaul
:) instead of the the last line above you can use

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
Avatar of Phillip Burton
Phillip Burton

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".