MySQL Select by Count + Sum of values

I have the following query (Wordpress DB).

This will return data for the comment that has the most combined "up" and "down" ratings:

$comment_query = $wpdb->get_results("
SELECT wp_comments.*, wp_comment_rating.*, (wp_comment_rating.ck_rating_up+wp_comment_rating.ck_rating_down) AS pop_comment 
FROM wp_comments, wp_comment_rating 
WHERE wp_comments.comment_post_ID = $post->ID 
AND wp_comments.comment_ID = wp_comment_rating.ck_comment_id 
AND wp_comments.comment_approved = 1 
ORDER BY pop_co开发者_开发知识库mment 
DESC 
LIMIT 1");

However, I'd also like to factor in comments that have the most replies by counting the number of matched "comment_parent" per comment, then adding that total to the "pop_comment" value I'm ordering by.

Essentially, I want to get the data for a comment with the most combined replies and up/down ratings.

Hope that makes sense...

You could subquery to get the counts by comment_parent

$comment_query = $wpdb->get_results("
SELECT wp_comments.*, wp_comment_rating.*, (wp_comment_rating.ck_rating_up+wp_comment_rating.ck_rating_down + (
    select count(*) from wp_comments c2 where c2.comment_parent=wp_comments.comment_ID
    )) AS pop_comment 
FROM wp_comments, wp_comment_rating 
WHERE wp_comments.comment_post_ID = $post->ID 
AND wp_comments.comment_ID = wp_comment_rating.ck_comment_id 
AND wp_comments.comment_approved = 1 
ORDER BY pop_comment 
DESC 
LIMIT 1");

This should work

Essentially its just a sub query to get the total comments for the comment_parent and then joins that as SubQ / SubCount to the field

comment_query = $wpdb->get_results(" 
SELECT wp_comments.*, wp_comment_rating.*, (SubCount + pop_comment) AS total_rank,    (wp_comment_rating.ck_rating_up+wp_comment_rating.ck_rating_down) AS pop_comment  
JOIN (SELECT COUNT(*) as SubCount, comment_parent as Sub_ID FROM wp_comments GROUP BY comment_parent) as SubQ
ON wp_comments.comment_ID = SubQ.Sub_ID
FROM wp_comments, wp_comment_rating  
WHERE wp_comments.comment_post_ID = $post->ID  
AND wp_comments.comment_ID = wp_comment_rating.ck_comment_id  
AND wp_comments.comment_approved = 1  ORDER BY total_rank  DESC  LIMIT 1");