Winning Usergroup SQL query??

[obiwan8472]

I have 5 usergroups.. i have an sql queries that tallies up all the points they have gathered.
What i want now is an sql queries to see which usergroup has the highest points from its members AND then display it.

So it looks at usergroup a, b, c, d and e and finds c has the highest number of points.

Each member has points on their profile.

Thanks

[g-force2k2]

obiwan,

try this query:

SELECT SUM(points) AS total
FROM user
GROUP BY usergroupid DESC LIMIT 1

Cheers,
g-force2k2

[obiwan8472]

Hi, thanks for that. It worked. but i decided I wanted the title to show up. So i put into my index.php thing and made a querie but it wont show up the title of the winning group at all.

$totalpoint=$DB_site->query_first('SELECT user.membergroupids,max(points) AS tt FROM user WHERE user.membergroupids GROUP BY user.membergroupids');
$totalx=$totalpoint[user.membergroupids];

$total=$DB_site->query_first('SELECT usergroup.title FROM usergroup WHERE usergroup.usergroupid="$totalpoint"');
$totalz=$total[usergroup.title];

any help?

[Xenon]

you just havt to join the usergroup table into g-force's query:


SELECT SUM(user.points) AS total, usergroup.title
FROM user
LEFT JOIN usergroup USING (usergroupid)
GROUP BY user.usergroupid DESC
ORDER BY total
LIMIT 1