Yes its close and thank you. The problem I am having is I am able to generate the correct ranking.id for that particular user but I can't seem to make it equal the ranking.rating.
ID ranking.rating 9 = 5.6 (example)
Here's my query:
SELECT routes.user_id, ranking.rating, ROUND(AVG(ranking.id), 0) avg_ranking, users.username, users.user_id, routes.rating FROM ranking, routes, users WHERE username='$username' AND routes.user_id = users.user_id AND ranking.rating = routes.rating GROUP BY routes.user_id
//echo some stuff out
echo("<td align='right'>".$row["ranking.rating"]." </td>");I know I need to make the avg_ranking or the ranking.id = ranking.rating but I can't seem to get it work. Any more suggestions? Again thanks for all your help.
-- Craig
On Aug 25, 2004, at 12:48 AM, Mark C. Stafford wrote:
Hi Craig,
It sounds to me as though you're practically there. My syntax is a little different when I do groupings. Here's what I got from your question. Is it what you wanted...or close enough to get you where you're going?
Good luck,
Mark
drop table if exists test.ranking;
create table test.ranking ( id int(3) unsigned auto_increment primary key , rating varchar(5) ); insert into test.ranking(rating) values('5.0'); insert into test.ranking(rating) values('5.1'); insert into test.ranking(rating) values('5.2'); insert into test.ranking(rating) values('5.3'); insert into test.ranking(rating) values('5.3a'); insert into test.ranking(rating) values('5.3b');
drop table if exists test.routes; create table test.routes ( user_id int(3) unsigned , rating varchar(5) ); insert into test.routes(user_id, rating) values(1, '5.2'); insert into test.routes(user_id, rating) values(1, '5.3'); insert into test.routes(user_id, rating) values(1, '5.3a');
SELECT routes.user_id , @avg:=ROUND(AVG(ranking.id), 0) avg_ranking FROM test.ranking , test.routes WHERE routes.user_id = 1 AND ranking.rating = routes.rating GROUP BY routes.user_id ;
SELECT * FROM test.ranking WHERE id = @avg ;
+----+--------+ | id | rating | +----+--------+ | 4 | 5.3 | +----+--------+
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
