Craig Hoffman wrote:
Mark,
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
<rant>9 is never equal to 5.6.</rant> Sorry, but I teach math and that sort of thing drives me nuts.
I mention this because I think it is part of why this is causing you trouble. For every row of ranking you've shown us, ranking.id does not equal ranking.rating, so you cannot "make it equal the ranking.rating". Yes, I know what you meant. You're using that as shorthand for "getting the corresponding ranking.rating for the ranking.id". But that shorthand obscures the problem. You need to select the rows for the given user to look at them to calculate the average. You need the resulting average id to look up the corresponding rating. You see? By avoiding the shorthand, I think it is a little more obvious that this takes 2 steps. You cannot magically select the row with the average id at the same time you are selecting the rows to be averaged.
If you want the average for a particular user, say user_id = 1, as in your example, you can do it in 2 steps with a user variable (see sample data at the end):
SELECT @avg_rank_id:=ROUND(AVG(ranking.id), 0) avg_rank_id FROM ranking, routes WHERE routes.user_id = 1 AND ranking.rating = routes.rating; +-------------+ | avg_rank_id | +-------------+ | 4 | +-------------+ 1 row in set (0.00 sec)
SELECT * FROM ranking WHERE id = @avg_rank_id; +----+--------+ | id | rating | +----+--------+ | 4 | 5.3 | +----+--------+ 1 row in set (0.00 sec)
If you want to get the average for each user_id, you can do it in 2 steps with a temporary table:
CREATE TEMPORARY TABLE rank_avg SELECT user_id, ROUND(AVG(ranking.id), 0) AS avg_rank_id FROM ranking, routes WHERE ranking.rating = routes.rating GROUP BY routes.user_id;
SELECT rank_avg.user_id, ranking.rating FROM rank_avg, ranking WHERE ranking.id = rank_avg.avg_rank_id; +---------+--------+ | user_id | rating | +---------+--------+ | 1 | 5.3 | | 2 | 5.3a | +---------+--------+ 2 rows in set (0.00 sec)
DROP TABLE rank_avg;
{I see that Shawn has sent you a solution equivalent to this as I was typing.}If you have mysql 4.1, you can use a (correlated) subquery to combine both steps in one query:
SELECT rt.user_id, rnk.rating
FROM routes rt, ranking rnk
WHERE rnk.id = (SELECT ROUND(AVG(ranking.id), 0)
FROM ranking, routes
WHERE ranking.rating = routes.rating
AND routes.user_id = rt.user_id)
GROUP BY rt.user_id;+---------+--------+ | user_id | rating | +---------+--------+ | 1 | 5.3 | | 2 | 5.3a | +---------+--------+ 2 rows in set (0.01 sec)
This will be inefficient, however, so I don't recommend it.
Finally, as a mathematician, I must point out that calling this the "average ranking" is almost certainly misleading. Aside from the rounding, you are treating your ratings as if they were on a linear scale, but their values imply otherwise. That is, what you are doing assumes that the difference between 5.3a and 5.3b is the same as the difference between 5.1 and 5.2. I don't know anything about your data other than what you've told us, but I'd be surprised if that assumption were accurate.
Michael
============================ Data for the above examples:
USE test; DROP TABLE IF EXISTS ranking; CREATE TABLE ranking ( id INT(3) UNSIGNED AUTO_INCREMENT PRIMARY KEY, rating VARCHAR(5) );
INSERT INTO ranking
VALUES (1, '5.0'), (2, '5.1'), (3, '5.2'),
(4, '5.3'), (5, '5.3a'), (6, '5.3b'),
(7, '5.4'), (8, '5.5'), (9, '5.6');DROP TABLE IF EXISTS routes; CREATE TABLE routes ( user_id INT(3) UNSIGNED, route CHAR(7), rating CHAR(4) );
INSERT INTO routes
VALUES (1, 'Route 1','5.2'), (1, 'Route 2', '5.3'), (1, 'Route 3', '5.3a'),
(2, 'Route 1','5.2'), (2, 'Route 2', '5.3'), (2, 'Route 3', '5.6');
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
