SUM returns double value

Question

A couple of days ago, somebody help me to approach this query. I'm trying to get the first 10 records of my DB. The problem is the points. When I use SUM on points field I get exactly the double of the real value in case of have more than 1 record of the same user... Do you know what am I doing wrong? Thank you

This is the code which returns just the first record of the user (doesn't SUM all the points of the same user)

SELECT *
FROM commerce
LEFT JOIN points ON points.id_com = commerce.id
LEFT JOIN comments ON comments.id_com = commerce.id AND (comments.validbycom = 1 AND  comments.validbyus = 1)
LEFT JOIN winners ON winners.id_com = commerce.id
GROUP BY commerce.id
ORDER BY SUM(points) DESC, COUNT(comments.id_com) DESC, commerce.date ASC, COUNT(winners.id_com) DESC LIMIT 10

This is the code which return exactly the double of the total

SELECT SUM(points)
    FROM commerce
    LEFT JOIN points ON points.id_com = commerce.id
    LEFT JOIN comments ON comments.id_com = commerce.id AND (comments.validbycom = 1 AND  comments.validbyus = 1)
    LEFT JOIN winners ON winners.id_com = commerce.id
    GROUP BY commerce.id
    ORDER BY SUM(points) DESC, COUNT(comments.id_com) DESC, commerce.date ASC, COUNT(winners.id_com) DESC LIMIT 10

So - one person wrote you incorrect query. Now you're looking for another person to fix it. What is your role in this task? A management? If so - it's the wrong place, SO is a community for developers. — zerkms, Jan 08 '14 at 00:16
I edit my question with the two queries I'm using and the problems I'm having — Gaby, Jan 08 '14 at 00:17
Welcome to SO. It's really hard to tell what you're trying to do from your question. I suggest you read up on `SUM()` and `GROUP BY`, and try again. — O. Jones, Jan 08 '14 at 00:19
Sorry. I'm not a manager and I'm not looking for a guru. I'm just trying to learn and sometimes, somewhere can teach... I don't want the right query, just a CLUE in what would be the problem. I think the problem can be the query applies SUM to times, because it's exactly the double. If you don't want to help me I understand, but I think is no necessary to attack anybody. — Gaby, Jan 08 '14 at 00:21
THANKS to anybody who likes HELP. I'm gonna try and I'll be back — Gaby, Jan 08 '14 at 00:22
I've found the problem! The points are in the points table. When I add the comments table the points duplicate the value. I just need the other tables to order in case of two persons with the same points... any clue? — Gaby, Jan 08 '14 at 00:42

score 1 · Accepted Answer · answered Jan 08 '14 at 02:44

Presumably, the points column comes from the points table. The extra joins are introducing extra rows. So, try aggregating before doing the join:

SELECT sumpoints, commerce.id
FROM commerce LEFT JOIN
     (select id_com, sum(points) as sumpoints
      from points
      group by id_com
     ) points
     ON points.id_com = commerce.id LEFT JOIN
     comments
     ON comments.id_com = commerce.id AND (comments.validbycom = 1 AND  comments.validbyus = 1) LEFT JOIN
     winners
     ON winners.id_com = commerce.id
GROUP BY commerce.id
ORDER BY sumpoints DESC, COUNT(comments.id_com) DESC, commerce.date ASC, COUNT(winners.id_com) DESC
LIMIT 10

that was EXACTLY what I wanted THANK YOUUUUUUUUU!!!!!!!!!!!!... I was suffering trying to learn subqueries because I've never use them. I was in the right way but you help to arrive at the end faster! Thank you again ;) — Gaby, Jan 08 '14 at 03:06

SUM returns double value

1 Answers1