[SOLVED] Count how often two keys are matching (SQL)

Count how often two keys are matching (SQL)

For example, I have the following table:

| comment_ID     | comment_author   | comment_author_email
| -------------- | ---------------- | ---------------------- |
| 100            | Max              | max@gmail.com          |
| 101            | Max              | max2@gmail.com         |
| 102            | Pierre           | pierre@gmail.com       |
| 103            | Max              | max@gmail.com          |
| 104            | Sam              |                        |
| 105            | Max              | max@gmail.com          |

The result / output of my SQL request should be:

Max: 3
Max: 1 /* because of the different email */
Pierre: 1
Sam: ignore (because no email exists)

My problem: With the following SQL request, I can get the count of one specific user (the logged-in user):

global $wpdb;
$count = $wpdb->get_var('
SELECT COUNT(comment_author)
FROM ' . $wpdb->comments. '
WHERE comment_author_email = "' . get_comment_author_email() . '"');
echo $count . ' comments';

BUT: How to get the count of all comments per user (by name + email), instead of only the logged-in one?

Solution

I think what you want here is:

SELECT comment_author, comment_author_email, COUNT(*) AS cnt
FROM comments
WHERE comment_author_email IS NOT NULL
GROUP BY comment_author, comment_author_email;

If you don't need the email, then remove it from the select clause.