distinct group of values

I have a table named AttendanceGrid and I have the following query below that finds the records from two separate fiscal years.  The problem with the output is that there are duplicate records.  I want to select only the unique records based on these columns:

[First Name]
[Last Name]
Birthdate


How can I modify the following code to select only those unique records?
select AttendanceGrid.Committee, count(Distinct AttendanceGrid.ID) as 'Core Males' from 
AttendanceGrid INNER JOIN tblRegistration ON AttendanceGrid.[ID]=tblRegistration.[ID] 
Where ((AttendanceGrid.Month IN ('November','December','January', 'February','March','April', 'May','June') And AttendanceGrid.Fiscal = 2013) or
(AttendanceGrid.Month IN ('July', 'August', 'September', 'October') And AttendanceGrid.Fiscal = 2014))
And tblRegistration.Combo5 = 'Male' And AttendanceGrid.Total > 0  And AttendanceGrid.[CSW/TitleXX] = 'T' 
group by AttendanceGrid.Committee order by Committee

Open in new window

al4629740Asked:
Who is Participating?
 
Jeff DarlingDeveloper AnalystCommented:
SELECT A.Committee
	,count(DISTINCT A.ID) AS 'Core Males'
FROM #AttendanceGrid A
INNER JOIN #tblRegistration R ON A.[ID] = R.[ID]
WHERE (
		(
			A.Month IN ('November','December','January','February','March','April','May','June')
			AND A.Fiscal = 2013
			)
		OR (
			A.Month IN ('July','August','September','October')
			AND A.Fiscal = 2014
			)
		)
	AND R.Combo5 = 'Male'
	AND A.Total > 0
	AND A.[CSW/TitleXX] = 'T'
	AND A.ID in (select min(ID) from #AttendanceGrid group by [First Name],[Last Name],BirthDate)
GROUP BY A.Committee
ORDER BY A.Committee

Open in new window

0
 
al4629740Author Commented:
Is there any reason you put a # sign in front of the tables?

I don't think the data output is correct.  I'm trying to see why
0
 
al4629740Author Commented:
I see the problem.  Some of the "duplicates" have different IDs.  They are still duplicates and have different IDs which is why  I am gathering the unique values based on First Name, Last Name, Birthdate.

SELECT A.Committee
	,count(DISTINCT A.ID) AS 'Core Males'
FROM AttendanceGrid A
INNER JOIN tblRegistration R ON A.[ID] = R.[ID]
WHERE (
		(
			A.Month IN ('November','December','January','February','March','April','May','June')
			AND A.Fiscal = 2013
			)
		OR (
			A.Month IN ('July','August','September','October')
			AND A.Fiscal = 2014
			)
		)
	AND R.Combo5 = 'Male'
	AND A.Total > 0
	AND A.[CSW/TitleXX] = 'T'
	AND A.ID in (select min(ID) from AttendanceGrid group by [First Name],[Last Name],BirthDate)
GROUP BY A.Committee
ORDER BY A.Committee

Open in new window


How can I do this, when some of the duplicates have different IDs.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Anthony PerkinsCommented:
Sorry Jeff, but it looks like the author has chosen to start a new thread and it has now moved on here:
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28628702.html
0
 
Jeff DarlingDeveloper AnalystCommented:
@acperkins Thanks. I will take a look at Q_28628702

@al4629740 I created a temp table with some data that I believe might represent your data.  The # in the front of the table name indicates it is a temp table.  It is much easier to trouble shoot using a small sample of test data.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
al4629740, do you still need help with this question?
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.