Average grade of student temporary table in SQL Server

Question

I have a database with the following tables:

Students_T (SNo, SDedc, SAddress)
Courses_T (CNo, CDesc)
CoursesRegister_T (CRNo, CR_CNo, CR_SNo, CRGrade)

I need to represent the following data:

For each student show:

Student desc, Course desc, Course grade, Grades average

And I need to do this for every course that the student register in.

For example:

if student A is registered to course B and his grade is 90 and he's also registered to course C and his grade is 70, I suppose to get the following table:

A B 90 80
A C 70 80

The important thing is that I need to use temporary tables with SELECT INTO syntax and I can't figure it out.

I'm using SQL Server.

Someone know how to do that?

EDIT:

I already did this:

select CR_ST_No, ST_Desc, CR_Desc, CR_Grade
into #Grades
from Students_T
left join CoursesRegister_T on CR_ST_NO = ST_No
where CRS_Desc is not null

select *
from #Grades 
drop table #Grades

and it's giving me table with all data I need except the average.

When I try to change the row select * to select *, avg(CR_Grade) it can't execute.

"I can't figure it out." Figure what out? How to use temp tables? How to select into temp tables? — Bernd Linde, May 04 '15 at 09:30
"it can't execute", why not? What is the error message and what does the error tell you? — Bernd Linde, May 04 '15 at 10:09
It's said - "Column '#Grades.CR_ST_No' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." — divelner, May 04 '15 at 10:12
And what does that error message tell you? What does it indicate? — Bernd Linde, May 04 '15 at 10:23
but #Grades.CR_ST_No is part of the group by. if i understood well what the group by means. — divelner, May 04 '15 at 10:30

score 0 · Accepted Answer · answered May 04 '15 at 10:39

This is a typical use case for a Window Function. You haven't provided any sample data and I can't test it right now, but something like that should work:

SELECT s.SDedc, c.CDesc, r.CRGrade, AVG(r.CRGrade) OVER (PARTITION BY s.SNo) AS average_grade 
    FROM Students_T             AS s 
    LEFT JOIN CoursesRegister_T AS r ON r.CR_SNo = s.SNo 
    LEFT JOIN Courses_T         AS c ON r.CR_CNo = c.CNo;

score 0 · Answer 2 · answered May 04 '15 at 10:40

From your question and the subsequent comments, you need to change your select-statement to include a group by clause.

Please read through the MSDN SELECT (Transact-SQL) article as to how a select statement is structure, with explanations and examples of each section.

The reason you are getting your error message is that the AVG is an aggregate function and so requires that all columns that are not part of the aggregate, to be included into the group by clause of your select statement.

Average grade of student temporary table in SQL Server

2 Answers2