MS SQL连接两表计算仅含数值的平均成绩及语法错误排查

Hey there, let's work through this issue step by step—you're already on the right path with joining the Students and Grades tables, but there are a couple of issues causing that syntax error and potential future problems:

First, let's address the immediate syntax error you're seeing:

Incorrect syntax near the keyword 'GROUP'. [156] (severity 15)

1. Wrong Clause Order: GROUP BY Must Come Before ORDER BY #

In MS SQL, the sequence of query clauses is strict. Your current query places ORDER BY before GROUP BY, which breaks the syntax rules. The correct order for these clauses is:
SELECT → FROM/JOIN → WHERE → GROUP BY → ORDER BY

2. Handling Non-Numeric Grades to Avoid Conversion Errors #

Your sample data has non-numeric grades like B and IB. If you try to cast these directly to numeric, you'll get a conversion error even after fixing the syntax. You have two good ways to handle this:

Option 1: Filter Out Non-Numeric Grades First

Use the ISNUMERIC() function to only include grades that can be converted to numbers in your calculation:

SELECT 
    students.PERSON_ID, 
    students.ENROLL_PERIOD, 
    AVG(CAST(grades.GRADE AS numeric)) AS Average_Grade
FROM Students 
INNER JOIN Grades ON Students.PERSON_ID = Grades.PERSON_ID 
WHERE 
    ENROLL_PERIOD IS NOT NULL 
    AND ENROLL_PERIOD <> ''
    AND ISNUMERIC(grades.GRADE) = 1 -- Keep only valid numeric grades
GROUP BY students.PERSON_ID, students.ENROLL_PERIOD -- Now GROUP BY is in the right spot
ORDER BY students.ENROLL_PERIOD ASC

Option 2: Use TRY_CAST for Safer Conversion (Recommended)

TRY_CAST is more forgiving: it returns NULL instead of throwing an error when it hits non-numeric values, and AVG() automatically ignores NULL values. This is great if you want to avoid filtering upfront and just exclude invalid grades from the average:

SELECT 
    students.PERSON_ID, 
    students.ENROLL_PERIOD, 
    AVG(TRY_CAST(grades.GRADE AS numeric)) AS Average_Grade
FROM Students 
INNER JOIN Grades ON Students.PERSON_ID = Grades.PERSON_ID 
WHERE 
    ENROLL_PERIOD IS NOT NULL 
    AND ENROLL_PERIOD <> ''
GROUP BY students.PERSON_ID, students.ENROLL_PERIOD
ORDER BY students.ENROLL_PERIOD ASC

What You'll Get With Your Sample Data #

Both queries will return the correct averages:

• For PERSON_ID 12401: Average grade of 5.5 (calculated from 4 and 7; B is excluded)
• For PERSON_ID 43245: Average grade of 12 (calculated from 12; IB is excluded)

内容的提问来源于stack exchange，提问作者Mikkelrh