MySQL SUM error

mysql sum is returning wild numbers

[newguy1395]

Ok here is my problem...I have using the SUM function to get a total from a points column, but based on a series_id within the same table. When I run the query it is giving me exponential numbers. Below is the query I am working with. Don't be to harsh on me please as I am new to the mysql world, and I'm sure there is a better way to pull this off and it work.

Table Names Are:
session_race
series
user

$query = "SELECT SUM(session_race.points) AS total_points, session_race.series_id, session_race.user_id, series.series_id, user.user_id, user.driver "." FROM session_race,series,user "." WHERE session_race.series_id=$series_id GROUP BY user.user_id ORDER BY total_points DESC LIMIT 0, 20";

$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_array($result)){

echo "<tr>\n<td>" . $row['user_id'] . "</td>
 <td>" . $row['total_points'] . "</td>\n
 </tr>\n";
}

[syber]

Yhe problem is being caused by having three tables in your FROM clause without showing haw they are linked. Your WHERE clause should contain something like:

WHERE session_race.series_id=series.series_id AND session_race.user_id = user.user_id

Otherwise, you will end up with way too many rows being returned.

[newguy1395]

Thank you for that, that did fix the main problem..now the rest of the problem is it's getting the SUM of points based on the user_id's from all the 'series', and displaying them all together where I need it to only display from the selected 'series'. I played around with it a bit but I can't get it to do this.

User_1: 100 points (series 1)
User_1: 100 points (series 1)
user_1: 80 points (series 2)

It is adding all these together when I just need it to add it by each series like:

User1: 200 points (series 1)
User:1 80 points (series 2)

Now my query looks like this:

$query = "SELECT SUM(session_race.points) AS total_points, session_race.series_id, session_race.user_id, series.series_id, user.user_id, user.driver "." FROM session_race,series,user "." WHERE session_race.series_id=series.series_id AND session_race.user_id=user.user_id GROUP BY user.user_id ORDER BY total_points DESC LIMIT 0, 10";

[syber]

You are grouping by user_id. If you want the totals to be based on series, you should be alos grouping by series_id. In your example it looks like you want to GROUP BY user.user=_id, series.series_id.

[newguy1395]

Thanks for the help once again, that got it working like it needed to be. I really appreciate the help.