Using Count() Question

[LucL]

I've got 2 tables. 1 is a user table the other is their trade ratings. just like on ebay: ex someuser(500)

trade ratings are expressed only as -1,0,1.

When querying the user DB I want to join their rating SUM onto some user table column (say u_rating) so that i don't have to have to seperate queries.

my ratings table looks something like this, where r_uid is the user id from the users table...

CREATE TABLE ratings (
r_id INTEGER UNSIGNED AUTO_INCREMENT,
r_tid INTEGER UNSIGNED DEFAULT 0,
r_uid INTEGER UNSIGNED,
r_rating ENUM('-1','0','1'),
r_comment VARCHAR(250),
r_response VARCHAR(250),

My user table looks something like...

CREATE TABLE users (
u_id INTEGER UNSIGNED AUTO_INCREMENT,
u_type VARCHAR(30) DEFAULT 'Regular',
u_email VARCHAR(255),
u_username VARCHAR(30),

Thanks for your help guys!
Luc L.

 

[LucL]

Bump. Someone must know this

 

[Kozusnik]

SELECT u_username, SUM(r_rating)
FROM users, ratings
WHERE r_uid=u_id

Have you tried something like that?

Mark

 

[LucL]

That won't work because I still have to specify which user I'm looking for in the where clause.

 

[LucL]

Figured it out. Had to do with a join statement.

Thanks for the help Mark.

 

[theEclipse]

LucL....

maybe post the correct query for future users seeking documentation?

Robert Carpenter
Remember....eternity is much longer than this ~80 years we will spend roaming this earth.
ô¿ô

http://www.robertcarpenter.net