Divide the sum of grades using pivot operator

SELECT
    Student, [English], [Mathematics], [Science], [Programming], [History]
FROM
    (SELECT Student, Grades, Subject 
     FROM Grade_Report) AS SOURCETABLE
PIVOT
    (SUM(Grades) 
        FOR Subject IN ([English], [Mathematics], [Science], [Programming], [History])
    ) AS PIVOTTABLE

Output:

Jamie   188.00  161.00  163.00  183.00  184.00
Jenny   175.00  173.00  174.00  172.00  172.00
Jerome  184.00  186.00  184.00  191.00  181.00

Expected output:

Jamie   94      80.50   81.50   91.50   92
Jenny   87.50   86.50   87      86      86
Jerome  92      93      92      95.50   90.50

Answer

Each student may have two marks in each subject? Make sure of that first of all

Try to use the average AVG(Grades)

SELECT
Student, [English], [Mathematics], [Science], [Programming], [History]
FROM
(SELECT Student, Grades, Subject 
 FROM Grade_Report) AS SOURCETABLE
PIVOT
(AVG(Grades) 
    FOR Subject IN ([English], [Mathematics], [Science], [Programming], 
 [History])
)

Advertisement

Answer