0

I have a database of students and grades across varying report cycles. Each Report Cycle has a unique ID and each subject also has a unique ID. Students can have more than one teacher per subject so I need to average the grades given and produce just the averaged grade for their attainment scores. An example of the raw data is below:

+--------------+------+-----+----+-------------+---+
| 223599152142 |   12 |  92 |  3 | Mathematics | 0 |
| 223599152142 |   12 |  92 |  3 | Mathematics | 3 |
| 223599152142 |   12 |  92 |  7 | History     | 3 |
| 223599152142 |   12 |  92 | 12 | Economics   | 3 |
| 223599152142 |   12 |  92 | 12 | Economics   | 4 |
| 223599152142 |   12 |  92 | 26 | Latin       | 2 |
| 223599152142 |   12 | 109 |  3 | Mathematics | 3 |
| 223599152142 |   12 | 109 |  3 | Mathematics | 4 |
| 223599152142 |   12 | 109 |  7 | History     | 3 |
| 223599152142 |   12 | 109 | 26 | Latin       | 2 |
| 223599152142 |   12 | 109 | 26 | Latin       | 3 |
| 223599152142 |   12 | 110 |  3 | Mathematics | 4 |
| 223599152142 |   12 | 110 |  7 | History     | 2 |
| 223599152142 |   12 | 110 |  7 | History     | 3 |
| 223599152142 |   12 | 110 | 26 | Latin       | 2 |
| 223599152142 |   12 | 110 | 26 | Latin       | 3 |
+--------------+------+-----+----+-------------+---+

I wish to average the GradeTransposeValue based on the SubjectID for each ReportCycleID, but I am getting odd results. I have set up the below query to just focus on one student and one report cycle to show the problem.

SELECT intSubjectID, txtCurrentSubjectReportName, 
       CAST(AVG(intGradeTransposeValue) AS decimal (5,2)) AS avg_attainment
FROM VwReportsManagementAcademicReports
WHERE intReportCycleAcademicYear = 2017 
     AND intNCYear >6 AND intGradeID = 1 
     AND txtSchoolID = 223599152142 
     AND intReportCycleID= 110
GROUP BY intSubjectID, txtCurrentSubjectReportName

This is producing this result

+--------------+-----------------------------+----------------+
| intSubjectID | txtCurrentSubjectReportName | avg_attainment |
+--------------+-----------------------------+----------------+
|            7 | History                     | 2.00           |
|           26 | Latin                       | 2.00           |
|            3 | Mathematics                 | 4.00           |
+--------------+-----------------------------+----------------+

However, if we look at Latin as an example, the original values were 3 and 2, so 3+2 = 5 / 2 should = 2.5 and not 2.

Any help on this would be much appreciated.

  • I am not sure why the pictures I added to show the examples are not showing? – MIS_Gem Apr 3 '18 at 14:38
9

You can't get more precision from an INT value than a whole number. You need to make them decimals before calculating:

CREATE TABLE [grades]
([intGradeTransposeValue]      INT,
 [txtCurrentSubjectReportName] NVARCHAR(50)
);

INSERT INTO [grades]
([intGradeTransposeValue],
 [txtCurrentSubjectReportName])
VALUES (2,'latin'), (3, 'latin');

--Returns whole number
SELECT AVG([intGradeTransposeValue])
FROM   [grades];

--Returns 2.5
SELECT AVG(CAST([intGradeTransposeValue] AS DECIMAL(5,2)))
FROM   [grades];

Ideally you'd just store the grades as DECIMAL values to begin with, but the above works in a pinch, too.

  • 6
    @GDavies Then why does it not have a check mark? – paparazzo Apr 3 '18 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.