SQL Server – Average Value Not Working: Troubleshooting Tips

castdecimalsql servert-sql

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.

Best Answer

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.

Related Question