TecNQ (5/8/2013)
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.
... How can I do the above but with DISTINCT in each COUNT() function?
Thanks
Easier than it looks:
SELECT DISTINCT
su.StudentID,
COUNT(DISTINCT su.QualificationCode) OVER (PARTITION BY su.StudentID) as QualsFound,
COUNT(DISTINCT su.UnitCode) OVER (PARTITION BY su.StudentID) as UnitsFound
FROM
tblStudentUnitRegister su
WHERE
su.QualificationCode IS NOT NULL
AND su.UnitCode IS NOT NULL
However, that is overkill, as you're simply using a single table. I would recommend swapping that to this:
SELECT
su.StudentID,
COUNT( DISTINCT su.QualificationCode) AS QualsFound,
COUNT( DISTINCT su.UnitCode) AS UnitsFound
FROM
tblStudentUnitRegister su
WHERE
su.QualificationCode IS NOT NULL
AND su.UnitCode IS NOT NULL
GROUP BY
su.StudentID
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA