SQL: CTE using IIF

Question

The problem I am trying to solve:

Write a CTE with a SELECT statement that returns one row for each student that has courses with these columns:

• The StudentID column from the Students table
• The sum of the course units in the Courses table

Write a SELECT statement that uses this CTE to return these columns for each student:

• The StudentID column from the CTE
• The sum of course units from the CTE
• An indication of whether the student is fulltime or parttime (Hint: To determine whether a student is fulltime, use the IIF function to test if the sum of course units is greater than 9.)
• The total tuition (Hint: To calculate the tuition, use the IIF function to determine whether a student is fulltime or partime. Then, multiply the sum of course units by the PerUnitCost column in the Tuition table and add that to either the FullTimeCost or PartTimeCost column in the Tuition table. To do that, use a cross join to join the CTE and the Tution tables. This makes the columns from the Tuition table available to the SELECT statement.)

What I am running:

WITH UnitsSummary AS (
SELECT Students.StudentID,SUM(CourseUnits) AS TotalUnits
FROM Students JOIN StudentCourses ON Students.StudentID = StudentCourses.StudentID
JOIN Courses ON StudentCourses.CourseID = Courses.CourseID
GROUP BY Students.StudentID,CourseUnits
)

SELECT StudentID, TotalUnits, IIF( SUM (TotalUnits) >9,'FUlltime','Parttime'),FullTimeCost + (TotalUnits * PerUnitCost) AS Tuition
FROM UnitsSummary CROSS JOIN Tuition

My error message:

Msg 8120, Level 16, State 1, Line 8 Column 'UnitsSummary.StudentID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Answer 1

You do not need to sum(TotalUnits) again. Whenever you use aggregated function you have to do group by non-aggregated field. Try the following

SELECT 
    StudentID, 
    TotalUnits, 
    IIF(TotalUnits > 9,'FUlltime','Parttime'),
    FullTimeCost + (TotalUnits * PerUnitCost) AS Tuition
FROM UnitsSummary 
CROSS JOIN Tuition