MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Create Table commentmeta(comment_id INT, meta_key VARCHAR(50), meta_value VARCHAR(50));

Insert Into commentmeta Values
(540,'Voting','Great'),
(540,'Country','UK'),
(560,'Voting','Great'),
(560,'Country','PL'),
(610,'Voting','Bad'),
(610,'Country','UK'),
(630,'Voting','Great'),
(630,'Country','UK');
Select T.meta_value As Country,
       COUNT(Case When D.meta_value = 'Great' Then 1 End) As Result
From
(
  Select comment_id, meta_value
  From commentmeta
  Where meta_key = 'Country'
) T
Join commentmeta D
On T.comment_id = D.comment_id
Where D.meta_key = 'Voting'
Group By T.meta_value


Country
Result


UK
2

PL
1


Select COUNT(*) Reslut From commentmeta T
Where T.meta_value = 'Great' 
And comment_id in (Select D.comment_id From commentmeta D Where D.meta_value = 'UK')


Reslut


2