Trying to get count on distinct multiple columns in SQL

I have three columns used in a table I'm trying to get a distinct count on. userID, messageFrom, messageDate.

messaging is the table name
messageDate is smalldatetime in the format of MM/DD/YY 12:00:00 AM (always 12am)
userID is int, to whom the message is going to
messageFrom is nvarchar(128), who's sending the message

I'm trying to find the count of how many unique messageFrom's are sent to a particular userID on a daily basis from the beginning of the year. In this code, userID 1008. What's desired is if 2 identical messageFrom's are sent in the same day, it only counts as one. If 2 identical messageFrom's are sent in two days, it counts as two.

I'm unsure if the code below is correct as it's showing data, just much less than I expected.


(SELECT DISTINCT messageFrom,messageDate 
FROM messaging 
WHERE userID='1008' 
AND messageDate >= DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) AS t

Open in new window

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
Try this:
SELECT DISTINCT messageFrom,messageDate, COUNT(*) over ( partition by messageFrom,messageDate) cnt, COUNT(*) over () dis_cnt
FROM messaging
WHERE     userID = '1008'
AND messageDate >= DATEADD(yy, DATEDIFF(yy, 0, getdate()),0)

Open in new window

sdelucAuthor Commented:
@rrjegan17: I came up with the same results using yours and what I originally had, trying different userID's, so I'm guessing they are the same.

If so, is one more efficient than the other?
Raja Jegan RSQL Server DBA & ArchitectCommented:
Check out the execution plans of both queries to decide which one is optimal based upon the indexes in your table.
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

sdelucAuthor Commented:
Ok. Your original post was a confirmation (and therefore a solution) of acquiring the correct data, and that's what I needed.

Thank you.
sdelucAuthor Commented:
A confirmation that I was retrieving the correct data from SQL.
Raja Jegan RSQL Server DBA & ArchitectCommented:
Requested you to confirm with Execution plan since missing indexes or additional indexes might change the performance of queries to great extent.
All Courses

From novice to tech pro — start learning today.