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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

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

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
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

From novice to tech pro — start learning today.