It's ok. I now understand the code, and how it relates to the query overall.
But I have a question... should be simple to answer.
I have the following code ...
SELECT DISTINCT su.StudentID,
COUNT(su.QualificationCode) OVER (PARTITION BY su.StudentID) as QualsFound,
COUNT(su.UnitCode) OVER (PARTITION BY su.StudentID) as UnitsFound
FROM tblStudentUnitRegister su
WHERE su.QualificationCode IS NOT NULL
AND su.UnitCode IS NOT NULL
What I am trying to do is report by each student ID, the number of Qualifications and Units.
But as you may have already seen, it's reporting the same COUNT() for each.
StudentID QualsFound UnitsFound
TECNQ13137 1 1
TECNQ11105 66 66
ATCNQ08015 68 68
TECNQ12016 46 46
TECNQ12354 128 128
TECNQ13090 1 1
TECNQ12265 45 45
TECNQ11201 12 12
How can I do the above but with DISTINCT in each COUNT() function?