I am trying to divide 2 subqueries. there is a comment in the query pointing out what I have unsuccessfuly tried. Can anyone lead me in the right direction please.
SELECT TOP 100 PERCENT dbo.tCourseDescriptions.CourseName, dbo.nrcAssignments.AssignmentTitle, dbo.nrcAssignments.AssignmentDueDate,
dbo.nrcStandard.StandardAbbreviation, dbo.nrcStandard.StandardDescription, dbo.nrcStandard.StateStandardID, dbo.nrcStandard.StandardName,dbo.nrcAssignments.SK AssignmentID,
(SELECT AVG(FinalGradeTotal) FROM dbo.nrcAssignmentGrades WHERE FKASsignmentID = dbo.nrcAssignments.SKAssignmentID AND Omitted <> '1') As MaxPoints,
(SELECT ROUND(AVG(GradePointValue),0) FROM dbo.nrcAssignmentGrades WHERE FKASsignmentID = dbo.nrcAssignments.SKAssignmentID AND Omitted <> '1')As ClassAvg,
(SELECT SUM(FinalGradeTotal) FROM dbo.nrcAssignmentGrades WHERE FKASsignmentID = dbo.nrcAssignments.SKAssignmentID AND Omitted <> '1') As MaxTotal,
(SELECT SUM(GradePointValue) FROM dbo.nrcAssignmentGrades WHERE FKASsignmentID = dbo.nrcAssignments.SKAssignmentID AND Omitted <> '1')As GPSum
-- here are the subqueries I am trying to divide.
-- I am trying to get a TRUE class average
---------------------------------------------------
,CASE WHEN 'ClassAvg' <> 0.0 THEN
((SELECT SUM(FinalGradeTotal) FROM dbo.nrcAssignmentGrades WHERE FKASsignmentID = dbo.nrcAssignments.SKAssignmentID AND Omitted <> '1')/
(SELECT SUM(GradePointValue) FROM dbo.nrcAssignmentGrades WHERE FKASsignmentID = dbo.nrcAssignments.SKAssignmentID AND Omitted <> '1')) ELSE 0 END As MyAvg
------------------------------------------------------
FROM dbo.tStudentEnroll INNER JOIN
dbo.tSchedule ON dbo.tStudentEnroll.FKClassID = dbo.tSchedule.SKClassID INNER JOIN
dbo.tCourseDescriptions ON dbo.tSchedule.FKCourseID = dbo.tCourseDescriptions.SKCourseID INNER JOIN
dbo.nrcxAssignmentSkills ON dbo.tStudentEnroll.FKClassID = dbo.nrcxAssignmentSkills.FKClassID INNER JOIN
dbo.nrcAssignments ON dbo.nrcxAssignmentSkills.FKAssignmentID = dbo.nrcAssignments.SKAssignmentID INNER JOIN
dbo.nrcStandard ON dbo.nrcxAssignmentSkills.FKStandardID = dbo.nrcStandard.SKStandardID
GROUP BY dbo.tStudentEnroll.FKClassID, dbo.tCourseDescriptions.CourseName, dbo.tCourseDescriptions.AcademicYear,
dbo.nrcxAssignmentSkills.FKStandardID, dbo.nrcAssignments.AssignmentTitle, dbo.nrcAssignments.AssignmentDueDate,
dbo.nrcStandard.StandardAbbreviation, dbo.nrcStandard.StandardDescription, dbo.nrcStandard.StateStandardID,
dbo.nrcStandard.StandardName,dbo.nrcAssignments.SK AssignmentID
HAVING (dbo.tStudentEnroll.FKClassID = 11204)
ORDER BY AssignmentDueDate DESC