mwheeler_fsd
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
"gratitude" :)
FROM MyTable
GROUP BY Col1, Col3