homepage Welcome to WebmasterWorld Guest from 23.20.63.27
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
issue with order by in mysql
Order by is adding all my values together
newguy1395




msg:4055698
 3:39 am on Jan 7, 2010 (gmt 0)

Hello all. I have a problem with group by and order by in mysql. For whatever reason that I can't figure out, it is adding my total_points values together for all my user_id's.

For Example:
User1: 330 (total_points)
User1: 310 (total_points)
User1: 280 (total_points)
User2: 330 (total_points)
User2: 310 (total_points)
User2: 280 (total_points)
And so on and so on for all my users. It adds them all together and displays each total_points value for each user_id. I hope I haven't confused you guys to much.

Here is how my table I am querying from is setup.

== Table structure for table race

¦------
¦Field ¦Type ¦Null ¦Default
¦------
¦//**id_race**//¦bigint(15) ¦No ¦
¦race_id ¦bigint(10) ¦No ¦0
¦user_id ¦bigint(15) ¦No ¦0
¦f ¦mediumint(5) ¦No ¦0
¦s ¦mediumint(5) ¦No ¦0
¦race_interval ¦varchar(255) ¦No ¦
¦race_laps ¦smallint(3) ¦No ¦0
¦led ¦varchar(50) ¦No ¦
¦points ¦mediumint(5) ¦No ¦0
¦total_points ¦mediumint(5) ¦No ¦0
¦race_status ¦varchar(50) ¦No ¦

This is my query I am using to get the results I need.

$r_query2=mysql_query("SELECT user_id, points, SUM(points) AS total_points FROM race GROUP BY user_id ORDER BY total_points DESC");
while($get_r_query2=mysql_fetch_array($r_query2))
{
echo "{$get_r_query3['driver']}"; //this query result works fine & is a seperate query all together
"{$get_r_query2['total_points']}";
}

 

LifeinAsia




msg:4056000
 5:02 pm on Jan 7, 2010 (gmt 0)

Not sure why you are getting both points and SUM(points) in your query. The following makes more sense:
SELECT user_id, SUM(points) AS total_points
FROM race
GROUP BY user_id
ORDER BY total_points DESC

newguy1395




msg:4056050
 6:07 pm on Jan 7, 2010 (gmt 0)

Ok I don't know why I was doing that either..lol I guess I was trying to make something work and forgot to remove that extra 'points' out. But I made the change to what you said, I still have the same issue. Below is the link to a test page to show what it is doing on my end. Maybe if you guys can see the exact problem I'm describing then you might have some ideas. Thanks for the help so far.

[66.191.161.110...]

LifeinAsia




msg:4056077
 6:48 pm on Jan 7, 2010 (gmt 0)

Try:
SELECT user_id, SUM(points) AS total_points
FROM race
GROUP BY user_id
ORDER BY SUM(points) DESC

newguy1395




msg:4056101
 7:07 pm on Jan 7, 2010 (gmt 0)

It gave the same result in testpoints2.php at the link above. I have a feeling it has something to do with the numerical data that is trying to be ordered. I just can't seem to find what is causing it to duplicate it over and over.

newguy1395




msg:4056107
 7:20 pm on Jan 7, 2010 (gmt 0)

Actually it may be, because I am using 2 separate queries to return driver and points. Below is the query exactly as I have it right now, and that produces the driver table like it should but it adds up all the total_points and assigns it to each driver.

Test example below:
[66.191.161.110...]


$r_query3=mysql_query("SELECT user_id, driver FROM user GROUP BY driver");
while($get_r_query3=mysql_fetch_array($r_query3, MYSQL_ASSOC))
{
$r_query2=mysql_query("SELECT user_id, SUM(points) AS total_points FROM race ORDER BY SUM(points) DESC");
while($get_r_query2=mysql_fetch_array($r_query2, MYSQL_ASSOC))
{
echo "<tr>\n<td>" . $get_r_query3['driver'] . "</td>
<td>" . $get_r_query2['total_points'] . "</td>\n
</tr>\n";
}
}

LifeinAsia




msg:4056121
 7:44 pm on Jan 7, 2010 (gmt 0)

Actually it may be, because I am using 2 separate queries to return driver and points.

Um, yeah! :)

You should combine the two together.

newguy1395




msg:4056161
 8:28 pm on Jan 7, 2010 (gmt 0)

Alright that fixed my problem. :-) Thank you for getting my brain working like it should be hehe.. Below is the code that I used to get it working for future reference for anyone that may need it.


$query = "SELECT user.user_id, user.driver, race.user_id, SUM(race.points) AS total_points "." FROM user, race "." WHERE user.user_id=race.user_id GROUP BY driver ORDER BY total_points DESC";
$result = mysql_query($query) or die(mysql_error());

// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
echo "<tr>\n<td>" . $row['driver'] . "</td>
<td>" . $row['total_points'] . "</td>
\n</tr>\n";
}

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