mysql statement help

[FiRe]

I have 3 tables:

+---------------------+
¦ groups ¦
+---------------------+
¦ id ¦ name ¦
+---------------------+

+---------------------+
¦ groupies ¦
+---------------------+
¦ userid ¦ groupid ¦
+---------------------+

+---------------------+
¦ users ¦
+---------------------+
¦ id ¦ user ¦ money ¦
+---------------------+

I am trying to list the top 10 groups by the total amount of money all of the users have in each group.
Its quite complicated and confusing the SQL statement with all the joins and stuff, so could someone help me put it right?

I have this so far:

SELECT groupies.groupid FROM groupies INNER JOIN users ON groupies.userid = users.id

But its not much use.
Thanks!

[FalseDawn]

SELECT GR.name, SUM(U.money) FROM ((groups GR INNER JOIN groupies GP ON GR.id=GP.groupid) INNER JOIN users U ON GP.userid=U.id) GROUP BY GR.name ORDER BY SUM(U.money) DESC LIMIT 10

[FiRe]

thanks but doesnt seem to work, phpmyadmin gives:

#1111 - Invalid use of group function

[the_nerd]

the 1111 error seams to stem from using the aggregate function (sum) in the order statement. Try this instead:

select sum(money) as m, groups.groupid
from groups join
groupies on groups.groupid = groupies.groupid join
users on groupies.userid = users.userid
group by groups.groupid
order by m desc
limit 0,10

pls note: I changed your id names to groupid and userid - makes the queries a lot easier to read.

have fun,

nerd.

[FiRe]

it works :-D

thank you!