SUM values from SQL column from same ID

Question

This is the comment_meta from wordpress. In there is a custom meta_key: rating.

I would like to get the SUM of all the ratings for that individual post.

The point being of this is to get the average from the user ratings. I have the total number of commenters, I just need the SUM.

The use of it is for the Schema.org AggregateReview: <meta itemprop="ratingValue" content=""/>.

I have tried to add the value via jQuery, but for some reason the schema isn't registering the text added later in the DOM.

var sum = 0;
$('.comment_rate').each(function(){
    numItems = $('.comment_rate').length
    sum += (parseFloat($(this).text()) / numItems);
    $('span[itemprop="ratingValue"]').text(sum);
});

ORIGINAL POST

I have this table (sorry, image is only way I know how to show):

I would like to sum the meta_value from the matching comment_id.

So far I have this but it SUMs the entire column not the ones matching the same id.

<?php
    $result = mysql_query('SELECT SUM(meta_value) AS value_sum FROM wp_play_commentmeta');
    $row = mysql_fetch_assoc($result);
    $sum = $row['value_sum'];
    echo $sum;
?>

Answer 1

Use a GROUP BY:

SELECT comment_id, SUM(meta_value) AS value_sum 
FROM wp_play_commentmeta
GROUP BY comment_id;

See it in action here:

• SQL Fiddle Demo.

---

If you want to do this for only those that had rating metakey, then add that in the WHERE clause. Something like this:

SELECT comment_id, SUM(meta_value) AS value_sum 
FROM wp_play_commentmeta
wHERE meta_key = 'rating'
GROUP BY comment_id;

• Updated SQL Fiddle Demo

---

Update:

JOIN the two tables, and GROUP BY post_id. Something like this:

SELECT 
  p.post_id,
  SUM(m.meta_value) AS value_sum
FROM wp_play_commentmeta    AS m
INNER JOIN wp_play_comments AS p ON m.comment_ID = p.comment_Id
wHERE meta_key = 'rating'
GROUP BY p.post_id;

---

Note that: Please stop using Mysql_* extensions, they are deprecated. Furtheremore your code this way is vulnerable to SQL Injection. Use PDO or prepared statements instead.