using SUM and GROUP BY when joining tables

[davidcriniti]

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

 

 

 

[Christian F.]

Posting what you've tried, as well as any error messages (or examples of wrong output), would be a lot of help. That way we can tell you what's wrong, why, and how to fix it.

[redarrow]

look up join , your nearly there.

 

http://www.tizag.com/mysqlTutorial/mysqljoins.php

[redarrow]

$query = "SELECT  member.member_firstname,member.member_lastname,SUM(points) FROM tbl_points WHERE member.member_id='points.member_points_id'"; 

try that but it needs loads more work.

Edited March 3, 2013 by redarrow

[Barand]

try

 

 

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

[davidcriniti]

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