Sum of values based on usergroupid, using two tables

[brownafroduck]

I'm a webmaster relatively inexperienced with databases and mySQL, so I am looking for any sort of pointer in the right direction like this. I've attempted to write a few SUM statements (is that even what you call them? statements?) using resources around the web, but I just can't seem to get this right.

I have two tables user and userfield.

In the user table I have the userid and usergroupid values.
In the userfield table I have the userid and userfield16 (an integer value representing points) values.

I would like to total the userfield16 points based on the usergroupid and have this value displayed on the forumhome index.php.

Make sense?

[Paul M]

This should do you for the sql;


SELECT usergroupid, SUM(userfield16) AS total FROM user
LEFT JOIN userfield USING (userid)
GROUP BY usergroupid

[brownafroduck]

Thanks for the quick reply!

I feel like I'm doing this completely incorrectly. This query gives me an array? Or what? I'm a complete noob at this, so here is my feeble attempt. I used the SQL that you gave me...

$result= mysql_query("
SELECT usergroupid, SUM(userfield16) AS total FROM " . TABLE_PREFIX . "user
LEFT JOIN " . TABLE_PREFIX . "userfield USING (userid)
GROUP BY usergroupid
") or die(mysql_error());

$points = mysql_fetch_array($result);

$group1= $points[0];
$group2= $points[1];
$group3= $points[2];
$group4= $points[3];


But I get this error...
Unknown column 'userfield16' in 'field list'

What am I doing wrong? What do I need to change? If I know that the usergroupid values that I need to sum up the points are 11, 12, 13, and 14, does that make it any easier? Is there perhaps some other approach that I can take to this with that knowledge?

Thanks!