FoxDonut FoxDonut - 7 months ago 30
SQL Question

SQL Server - Calculate Student GPA and Insert Into Table Variable

I'm trying to select each student's grades, add them up, and calculate their GPA. Lastly, the Student's name and GPA will be put into a row.

This is what I've started on:

Declare @NumClassesTaken int --Counts number of classes
Declare @GradesEarned decimal(2,1) --Accumulative Grades
Declare @GPA decimal(2,1) --@GradesEarned/@NumClassesTaken

Declare @GPATable TABLE ( StudID int, StudGPA decimal(2,1))


The table that all my data will be coming from is named [Enrollment], simlar to the below, but with far more data:

| StudID | SectionNum | Grade |

123 7 3.0
123 3 2.0
702 8 3.5
702 5 2.5
664 4 1.0
664 7 3.5
992 2 4.0


What I'd want the finished product to look like (Grouped By GPA):

| StudID | GPA |

992 4.0
702 3.0
123 2.5
664 2.2


I'm quite new to SQL so I would really like some assistance with this. It seems pretty simplistic, but I can't really figure out where to start. Is there a way to say for each StudID, sum up all values in the Grade column?

Thanks in advance!

Answer

From my understanding, this is what you're looking for.

DECLARE @StudentGrades TABLE(StudentId INT, ClassId INT, Grade DECIMAL(4,2))

-- Sample Entries:
INSERT INTO @StudentGrades
(StudentId, ClassId, Grade)
VALUES (1, 1, 60.0), (1, 2, 70.0), (2, 1, 75.0), (2, 2, 90.0);

-- Check table values.
SELECT * FROM @StudentGrades;

-- This is the report you were looking for.
SELECT StudentId, CONVERT(DECIMAL(4,1), AVG(Grade)) As [GPA]
FROM @StudentGrades
GROUP BY StudentId;
Comments