( SELECT stuid as id, CAST(sum(case when `subject`='math' then score end) AS SIGNED) as math, CAST(sum(case when `subject`='english' then score end) AS SIGNED) as english, CAST(( sum(case when `subject`='math' then score end) + sum(case when `subject`='english' then score end) ) AS SIGNED) as 'sum' FROM score GROUP BY stuid ) UNION ( SELECT 'avg' as id, CAST(avg(case when `subject`='math' then score end) AS SIGNED) as math, CAST(avg(case when `subject`='english' then score end) AS SIGNED) as english, CAST(( avg(case when `subject`='math' then score end) + avg(case when `subject`='english' then score end) ) AS SIGNED) as 'sum' FROM score )