homepage Welcome to WebmasterWorld Guest from 54.145.172.149
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
MySQL SUM error
mysql sum is returning wild numbers
newguy1395



 
Msg#: 4057723 posted 11:49 am on Jan 10, 2010 (gmt 0)

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

10+ Year Member



 
Msg#: 4057723 posted 3:30 pm on Jan 10, 2010 (gmt 0)

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



 
Msg#: 4057723 posted 6:32 pm on Jan 10, 2010 (gmt 0)

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

10+ Year Member



 
Msg#: 4057723 posted 8:31 am on Jan 11, 2010 (gmt 0)

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



 
Msg#: 4057723 posted 6:44 pm on Jan 11, 2010 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved