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

user_4399382 0
Viewed 58

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?

sql database
1 Answers

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.

user_127059120
Related