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?
[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 CommitteeASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
How can I do this, when some of the duplicates have different IDs.
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.CommitteeHow can I do this, when some of the duplicates have different IDs.
Sorry Jeff, but it looks like the author has chosen to start a new thread and it has now moved on here:
https://www.experts-exchange.com/questions/28628702/Finding-distinct-values-between-two-fiscal-years.html
https://www.experts-exchange.com/questions/28628702/Finding-distinct-values-between-two-fiscal-years.html
@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.
@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.
al4629740, do you still need help with this question?
ASKER
I don't think the data output is correct. I'm trying to see why