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 |