How to do this?

PHP
1

I’m creating a golfpage where you can compete in groups and want to compare each groups average points and then sort them descending. I have figured out how to get 1 groups average points like this:

$sql = "SELECT AVG(point_total) AS avg_stfpoints FROM ".$prefix."_score_info si
	INNER JOIN ".$prefix."_club_users cu ON cu.new_userid = si.user
	WHERE cu.clubid='$groupID'";

But how do I compare this against all the other groups?

Any ideas will be greatly apreiciated…

Thanks in advance :slight_smile:

2

Time to learn some GROUP BY.

SELECT cu.clubid,AVG(point_total) AS avg_stfpoints FROM “.$prefix.”_score_info si
INNER JOIN “.$prefix.”_club_users cu ON cu.new_userid = si.user
GROUP BY cu.clubid ORDER BY avg_stfpoints DESC;

3

Ahh, yes, but what if I want to get the clubdata into this query? Ive tryid this but with no luck:

SELECT cu.clubid, AVG(point_total) AS avg_stfpoints, c.clubname, c.ccountry, c.seoname FROM ".$prefix."_score_info si 
							INNER JOIN ".$prefix."_club_users cu ON cu.new_userid = si.user 
							GROUP BY cu.clubid
							INNER JOIN ".$prefix."_club c ON c.clubid = cu.clubid
							ORDER BY avg_stfpoints DESC;";

Any ideas?

4

you’ve mixed your clauses.

INNER JOIN is part of the FROM clause. (Think of it as an ‘and’).
GROUP BY is it’s own clause, which comes after the entirity of the FROM clause.