CODEWITHSUNDEEP
mysqlsumone-to-many
Mohammad Hossein Fattahizadeh
Mohammad Hossein Fattahizadeh

Reputation: 2731

sql sum using one to many relations

i have two tables

comments
------------------
id       text

rates
---------------------
id    comment_id    score

My query is ... but sum operation not affected. just one row will be returen.

SELECT *, SUM(rates.score) FROM comment LEFT JOIN rate ON (rate.comment_id = comment.id)

Upvotes: 3

Views: 7079

Answers (3)

Zane Bien
Zane Bien

Reputation: 23125

Your current query will simply sum the ratings of ALL comments. What you must do is incorporate GROUP BY which will group the aggregation by each comment, and so the result of the SUM() will only be the sum of ratings for each particular comment:

SELECT a.*, SUM(b.score) AS ratingsum
FROM comment a
LEFT JOIN rate b ON a.id = b.comment_id
GROUP BY a.id

Upvotes: 4

Mike Purcell
Mike Purcell

Reputation: 19989

Try adding a group by clause:

 SELECT comment.*, SUM(rates.score) FROM comment LEFT JOIN rate ON rate.comment_id = comment.id GROUP BY rates.comment_id;

Upvotes: 2

vearutop
vearutop

Reputation: 4072

SELECT comment.*, SUM(rates.score) FROM comment LEFT JOIN rate ON (rate.comment_id = comment.id)
GROUP BY comment.id

Upvotes: 4

Related Questions