Jump to content

using SUM and GROUP BY when joining tables


davidcriniti
Go to solution Solved by Barand,

Recommended Posts

Hi everyone,

 

I'm trying to give my students points for doing various things on my website. Eg: Completing a quiz, getting a certain score, all result in points, which are thrown into a table called points.

 

Now I'm trying to extract a leaderboard for the table.

 

I'm halfway there, and have a list of scores ranked from highest to lowest, next to the member's id.

 

See code below:

 

$query = "SELECT point_member_id, SUM(points) FROM tbl_points GROUP BY point_member_id ORDER BY SUM(points) DESC"; 
	 
$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
	echo  $row['point_member_id']. " : ". $row['SUM(points)'];
	echo "<br />";
}
	  
	

 

However, I really don't want point_member_id displaying. I'd like the student's firstname and lastname displaying.

 

firstname and lastname are the fields 'member_firstname' and 'member_lastname' in the table 'members'.

 

So what I need to do is extract this information where members.member_id = points.point_member_id

 

I've tried a bunch of variations to the query and searched ad nauseum, but nothing is helping.

 

Any advice would be greatly appreciated.

 

Cheers,

 

Dave

 

 

 

Link to comment
Share on other sites

Thankyou all. That worked perfectly Barand!

 

Might print out the finaly solution from Barand, as well as the echoing in case it is of use to any newbies.

 

  $query = "SELECT m.member_firstname, m.member_lastname, SUM(points) as total_points
FROM tbl_points as p
    INNER JOIN members as m ON p.point_member_id = m.member_id
GROUP BY p.point_member_id
ORDER BY total_points DESC";	 
	
	 
$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result)){
	echo  $row['member_firstname']. " " . $row['member_lastname'] . " : "  . $row['total_points'];
	echo "<br />";
}
	  
	

 

ps: Red arrow, you were VERY generous saying that I was close! :-)  I think there's a very big gap between the simple examples on sites such as that one and the solution provided here. I am very appreciative of the help! 

 

Cheers,

Dave

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.