Only count SQL entry if two rows (as a pair) have the needed conditions

How can I only count the “Great” per “Country” in this SQL table?

My table “commentmeta“

comment_id	meta_key	meta_value
540	Voting	Great
540	Country	UK
560	Voting	Great
560	Country	PL
610	Voting	Bad
610	Country	UK
630	Voting	Great
630	Country	UK

The result should be UK: 2 times “Great”, PL: 1 time “Great”

With something like this I just get the allover count (all “Greats”, not per country):

<?php global $wpdb;
$votes = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->commentmeta WHERE meta_key = 'country' AND meta_value = 'UK'" );
echo '<div class="votes">'. $votes .'</div>';?>

How to combine it with the “comment_id” to get the connection between “Voting” & “Country”, so that I can count it per country?

Answer

You may try the following to get all counts:

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

Also, you may try the following to get the count for a specific country:

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

See a demo from db<>fiddle.