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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 & Architect, EE Solution GuideCommented:
Check out the execution plans of both queries to decide which one is optimal based upon the indexes in your table.
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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 & Architect, EE Solution GuideCommented:
Requested you to confirm with Execution plan since missing indexes or additional indexes might change the performance of queries to great extent.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.