Seems simple enough - you just need a
CASE
statement to map the subject name, and a
GROUP BY
to group the rows with the same subject.
The only slightly tricky part is that you have to repeat the
CASE
statement in the
GROUP BY
clause.
SELECT
CASE
WHEN Subject In ('PHYSICS', 'CHEMISTRY', 'BIOLOGY') THEN 'SCIENCE'
ELSE Subject
END As Subject,
Sum(Marks1) As Marks1,
Sum(Marks2) As Marks2,
Sum(Marks1 + Marks2) As Total
FROM
YourTable
GROUP BY
CASE
WHEN Subject In ('PHYSICS', 'CHEMISTRY', 'BIOLOGY') THEN 'SCIENCE'
ELSE Subject
END
;