insert into score (id, name, sex, class, Chinese, math, English) 
Values (1, 'Peter', 'boy, 1,75,85,69); 
Insert into score (id, name, sex, class, Chinese, math, English) 
Values (2, 'Ken', 'boy, 1,55,56,89); 
Insert into score (id, name, sex, class, Chinese, math, English) 
Values (3, 'Anne', 'girl', 2,66,85,69); 
Insert into score (id, name, sex, class, Chinese, math, English) 
Values (4, 'Sue', 'girl', 2,62,77,88); 
Insert into score (id, name, sex, class, Chinese, math, English) 
Values (5, 'June', 'girl', 1,55,66,88); 

CodePudding user response:

 select class, 
The sum (case sex when 'boy' then one else 0 end) as boy_total, 
The sum (case sex when 'girl' then one else 0 end) as girl_total, 
The sum (Chinese) as chinese_total, 
The sum (math) as math_total, 
The sum (English) as english_total from score group by class.