MySQL Forums
Forum List  »  PHP

MySQL SUM problem
Posted by: chicken bawk
Date: October 16, 2007 01:26PM

SELECT
SUM(table2.vote) as votes,
COUNT(DISTINCT table3.id) as comments,
table1.*
FROM table1
LEFT JOIN table2 ON table2.id = table1.id
LEFT JOIN table3 ON table3.id = table1.id
GROUP BY table1.id;

My votes SUM is adding its values properly but if their are comments it than takes the SUM of votes and multiplies it to the COUNT of comments.

Ex: if comments returns '4' for id 1, and vote=4 +1 ints, the SUM of vote = 16.
Votes are 1 or -1 ints.

Removing The left join for table3, and removing the COUNT, the SUM(votes) returns exactly what i want.

CURRENT FIX: I changed it to be like this and i get the results i want, but is this the proper way to do what i want?
SELECT
(SELECT SUM(table2.vote) as votes FROM table2 WHERE table2.id = table1.id) as votes,
COUNT(DISTINCT table3.id) as comments,
table1.*
FROM table1
LEFT JOIN table3 ON table3.id = table1.id
GROUP BY table1.id;



Edited 2 time(s). Last edit at 10/16/2007 01:40PM by chicken bawk.

Options: ReplyQuote


Subject
Written By
Posted
MySQL SUM problem
October 16, 2007 01:26PM
October 16, 2007 01:57PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.