SQL QUERY - Count Results of a Column based on uniqueness of another Column

Hello,

I have a query situation that is not coming clear to me, I would appreciate an experts help on the following:

DB: MSSQL 2005

Scenario:

I have data that looks similar to this:

 Col1      Col2      Col3
ABCDE  12345    07/11/2006
FGHIJ    65431    07/11/2006
ABCDE  12345    07/11/2006
XYXSY  55321    07/11/2006
DFJGH  35332    07/11/2006
ABCDE  12345    07/12/2006
HFGGS  35530   07/12/2006
ABCDE  65432   07/12/2006
LJJSF   55351   07/12/2006

I am trying to build a query that will count, by day, the number of occurrences of COL1, but COL2 must be unique. So, in this example, for 07/11/2006 ABCDE would have a count of 1, because COL2 is a duplicate. In other words, it is ok for COL1 to duplicate, but not COL2. For 07/12/2006, ABCDE would have a count of 2 - two occurrences because COL2 is unique in each case. I have had this working when the requirement was only to count COL1 by day. When we added the described criteria, I haven't been able to get my arms around it. I've been working with sub queries, but can't seem to get the results I am looking for.

I hope this makes sense. Your assistance is appreciated.

Best,
mwheeler_fsd
mwheeler_fsdAsked:
Who is Participating?
 
FDzjubaConnect With a Mentor Commented:
SELECT col1,,col3,COUNT(DISTINCT col1+col2)
      FROM Table
GROUP BY col1,col3
0
 
DireOrbAntCommented:
SELECT Col1, Col3, count(*) AS 'Count'
FROM MyTable
GROUP BY Col1, Col3
0
 
mwheeler_fsdAuthor Commented:
Thanks to all for their input.

The COL1+COL2 got the uniqueness required. I did not realize you could compound. Many thanks - I also learned a new technique!

Points awarded and gratitute to all.

Best,
mwheeler_fsd
0
 
mwheeler_fsdAuthor Commented:
"gratitude" :)
0
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.

All Courses

From novice to tech pro — start learning today.