count number of user comments with a specific comment meta value

Your basic question is a pure SQL question.

$count = $wpdb->get_var(
  'SELECT COUNT( comments.comment_ID ) FROM '. $wpdb->comments .' as comments
  LEFT JOIN '.$wpdb->commentmeta.' AS cmeta ON comments.comment_ID = cmeta.comment_id
  WHERE user_id = 1
  AND comment_approved = "1"
  AND comment_type NOT IN ("pingback", "trackback" )
  AND cmeta.meta_key = "rating"
  AND cmeta.meta_value = 5'
);

I cleaned that up a bit to be more readable.

What you are doing with that SQL is pretty close, possibly identical to, what you’d get with ….

$args = array(
  'type' => '',
  'user_id' => '1',
  'count' => true,
  'meta_key' => 'rating',
  'meta_value' => '5',
);
$count = get_comments($args);

You aren’t explicitly excluding trackbacks and pingbacks with that but I am pretty sure that trackbacks and pingbacks will never have an associated user ID, so you are excluding them by that mechanism.