By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable(
Name VARCHAR(7) NOT NULL PRIMARY KEY
,Chemistry VARCHAR(11) NOT NULL
,Physics VARCHAR(11) NOT NULL
,Biology VARCHAR(11) NOT NULL
,Maths VARCHAR(6) NOT NULL
);
INSERT INTO mytable(Name,Chemistry,Physics,Biology,Maths) VALUES ('John','Excellent','Good','Good','Poor');
INSERT INTO mytable(Name,Chemistry,Physics,Biology,Maths) VALUES ('Kelvin','Excellent','Poor','Excellent','Poor');
2 rows affected
select
name,
sum(case when lvl = 'Excellent' then 1 else 0 end) Excellent,
sum(case when lvl = 'Good' then 1 else 0 end) Good,
sum(case when lvl = 'Poor' then 1 else 0 end) Poor
from (
select name, chemistry lvl from mytable
union all select name, physics from mytable
union all select name, biology from mytable
union all select name, maths from mytable
) t
group by name
| name | Excellent | Good | Poor |
|---|---|---|---|
| John | 1 | 2 | 1 |
| Kelvin | 2 | 0 | 2 |
select
t.*,
case
when Excellent > Good and Excellent > Poor then 'Excellent'
when Good > Excellent and Good > Poor then 'Good'
when Poor > Excellent and Poor > Good then 'Poor'
else 'Tie'
end [Max]
from (
select
name,
sum(case when lvl = 'Excellent' then 1 else 0 end) Excellent,
sum(case when lvl = 'Good' then 1 else 0 end) Good,
sum(case when lvl = 'Poor' then 1 else 0 end) Poor
from (
select name, chemistry lvl from mytable
union all select name, physics from mytable
union all select name, biology from mytable
union all select name, maths from mytable
) t
group by name
) t
| name | Excellent | Good | Poor | Max |
|---|---|---|---|---|
| John | 1 | 2 | 1 | Good |
| Kelvin | 2 | 0 | 2 | Tie |