Solved

Count number of unique records for each user

Posted on 2014-11-07
3
144 Views
Last Modified: 2014-11-07
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 :)
0
Comment
Question by:webressurs
  • 2
3 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40427944
Select Reciever, Count(Distinct Sender) as Contacts
From tblMessage
Group by Receiver
Order by Count(Distinct Sender) DESC
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40427973
:) 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
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40428030
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".
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
export sql results to csv 6 34
Sql Server group by 10 25
Update a text value in another table 10 37
SSRS Enable Remote Errors 4 23
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question