MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.23


create table affliation_details
( 
   affiliation_country varchar(50),
   artsubareakeyword1 varchar(50),
   artsubareakeyword2 varchar(50),
   artsubareakeyword3 varchar(50)
);
insert into affliation_details values ('Spain', 'Cell membranes', 'Cell staining', 'Communo percipitation');
insert into affliation_details values ('Kazakhstan', 'Factor analysis', 'Human Performance', 'Immuno Fluoroscense');
insert into affliation_details values ('Japan', 'Bone Marrow', 'Diagnostic Medicine', 'Genetic Loci');
insert into affliation_details values ('Kazakhstan', 'Drug Research', 'Factor analysis', 'Human Performance');
select affiliation_country, artsubareakeyword1 from affliation_details


affiliation_country
artsubareakeyword1


Spain
Cell membranes

Kazakhstan
Factor analysis

Japan
Bone Marrow

Kazakhstan
Drug Research


select 
t1.affiliation_country, t1.keyword, count(t1.keyword) as count_keyword
from
(
select affiliation_country, lower(artsubareakeyword1) keyword from affliation_details
union all
select affiliation_country, lower(artsubareakeyword2) from affliation_details
union all
select affiliation_country, lower(artsubareakeyword3) from affliation_details
) t1
group by
t1.affiliation_country, t1.keyword
order by
count(t1.keyword) desc


affiliation_country
keyword
count_keyword


Kazakhstan
factor analysis
2

Kazakhstan
human performance
2

Spain
cell membranes
1

Japan
bone marrow
1

Kazakhstan
drug research
1

Spain
cell staining
1

Japan
diagnostic medicine
1

Spain
communo percipitation
1

Kazakhstan
immuno fluoroscense
1

Japan
genetic loci
1


select 
max(t1.keyword) as count_keyword
from
(
select affiliation_country, lower(artsubareakeyword1) keyword from affliation_details
union all
select affiliation_country, lower(artsubareakeyword2) from affliation_details
union all
select affiliation_country, lower(artsubareakeyword3) from affliation_details
) t1
group by
t1.affiliation_country, t1.keyword


count_keyword


cell membranes

factor analysis

bone marrow

drug research

cell staining

human performance

diagnostic medicine

communo percipitation

immuno fluoroscense

genetic loci
affiliation_country	artsubareakeyword1
Spain	Cell membranes
Kazakhstan	Factor analysis
Japan	Bone Marrow
Kazakhstan	Drug Research