Problem with SUM & JOIN

[Bradley99]

Hi,

 

I have some code, which works, but not the way i wan't it too. The code should take win_points from each user in the BETTING table & Add them up, i.e giving the sum. Then the JOIN would display the username linking to the user_id from the betting table.

 

What it's doing now is just displaying me (Admin - User ID #1) with the SUM of ALL win_points of EVERY user in the BETTING table.

 

ANy idea's? Thanks

 

<? 
$standing=mysql_query("SELECT username, SUM(b.win_points) as total_points
FROM users AS u
LEFT JOIN betting AS b 
ON b.user_id = id
ORDER BY total_points");
while($the=mysql_fetch_object($standing)){

echo "  <tr><td class='profilerow'>$the->username</td><td class='profilerow'>$the->total_points</td>
  
   </tr>"; } ?>

[scootstah]

I don't think a JOIN will help here; you need a sub query. Try something like this:

SELECT username, (SELECT SUM(win_points) FROM betting WHERE user_id = id) AS total_points
FROM users;

[kicken]

You just need to include a GROUP BY for the username.

 

SELECT username, SUM(b.win_points) as total_points
FROM users AS u
LEFT JOIN betting AS b 
ON b.user_id = id
GROUP BY u.username
ORDER BY total_points

[Bradley99]

Thanks for moving Kicken, also thanks for the help, worked perfect

[ezekielnin]

maybe you could also create a view for it :

http://www.mysqltutorial.org/create-sql-views-mysql.aspx