MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tb_count(
    id int not null auto_increment,
    col1 enum('yes', 'no'),
    col2 enum('yes', 'no'),
    col3 enum('yes', 'no'),
    col4 enum('yes', 'no'),
    primary key(id)
) default charset=utf8;

insert into tb_count values (null, 'yes', 'yes', 'no', 'yes');
insert into tb_count values (null, 'no', 'yes', 'no', 'no');
insert into tb_count values (null, 'no', 'yes', 'no', 'yes');
insert into tb_count values (null, 'no', 'no', 'no', 'no');

select * from tb_count;


id
col1
col2
col3
col4


1
yes
yes
no
yes

2
no
yes
no
no

3
no
yes
no
yes

4
no
no
no
no


SELECT 'yes' AS value,
       SUM( col1 = 'yes' ) AS col1,
       SUM( col2 = 'yes' ) AS col2,
       SUM( col3 = 'yes' ) AS col3,
       SUM( col4 = 'yes' ) AS col4 
 FROM tb_count
UNION ALL
SELECT 'no',
       SUM( col1 = 'no' ),
       SUM( col2 = 'no' ),
       SUM( col3 = 'no' ),
       SUM( col4 = 'no' )
 FROM tb_count
ORDER BY value DESC 


value
col1
col2
col3
col4


yes
1
3
0
2

no
3
1
4
2


WITH t(value) AS
(
 SELECT 'yes' UNION ALL
 SELECT 'no'
)
SELECT value,
       SUM( col1 = value ) AS col1,
       SUM( col2 = value ) AS col2,
       SUM( col3 = value ) AS col3,
       SUM( col4 = value ) AS col4 
 FROM tb_count,
      t
GROUP BY value
ORDER BY value DESC


value
col1
col2
col3
col4


yes
1
3
0
2

no
3
1
4
2