Solved

Count number of unique records for each user

Posted on 2014-11-07
3
146 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL 10 35
Select single row of data for each ID in Select Statement 7 26
SQL R 21 25
How to use spatial data types in SQL Server Database project 2 21
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

839 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