averaging help

Hey Everyone,
I can you some assistance on this query. Its been driving me nuts for a few days...

I have three tables one is called ranking, routes and finally the users table. The users table is not really important.
The ranking table looks like this:

id rating
1 5.0
2 5.1
3 5.2
4 5.3
5 5.3a
6 5.3b
and so on...

The routes table looks like this: (FYI: The ID col in the ranking table has no relation to the user_id col.)


user_id route rating
1 somename 5.2
1 " " 5.3
1 " " 5.3a

Here's my query:
SELECT ranking.rating, AVG(id), users.username, users.user_id, routes.rating, routes.user_id FROM ranking, routes, users WHERE username='$username' AND users.user_id = routes.user_id AND ranking.rating = routes.rating GROUP BY username

What I am trying to do is find the average rating for this user. For example, this user has three entries. Unfortunately the rating scale uses letters which complicates matters. The ranking table should fix this so a 5.3a would equal a 5 and a 5.2 would equal 4 and so on. See below.

Here what I would like to do: This user has 3 enties and I would like to find the average those ratings.

5.2 = 3
5.3 = 4
5.3a = 5
_______________

3 + 4 + 5 = 12 / 3 = 4

So 4(avg) = 5.3(rating)
The average for this user would be 5.3. Any help would be most appreciated.
Craig

I think I got it figured out. I had to write 2 queries. One to generate the avg and the other to convert the avg to a rating (id =rating).


http://forums.devarticles.com/newreply.php?do=newreply&noquote=1&p=35881#