• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 166
  • Last Modified:

Count number of unique records for each user

I have a table (tblMessage) that contains a lot of messages:


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 :)
  • 2
1 Solution
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Select Reciever, Count(Distinct Sender) as Contacts
From tblMessage
Group by Receiver
Order by Count(Distinct Sender) DESC
:) 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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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".
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now