MySQl Average problem

[mousey182]

Hey Guys,
I have the following code, which averages the scores in a select statement and works no problem.

SELECT AVG(score) as 'avg' FROM
   (SELECT d_key_id, frequency, weighting,(frequency*weighting) as 'score'
   FROM users_to_keywords
   WHERE s_user_id=$user_id
   AND d_key_id = $score_key 
   ORDER BY score DESC)a

However, as there are some duplicated d_key_id's, i get dupliacted averages so need to use some form of group by function, I've tried the following code, but it throws up a load of errors!

SELECT d_key_id, AVG(score) as 'avg' FROM
   (SELECT d_key_id, frequency, weighting,(frequency*weighting) as 'score'
    FROM users_to_keywords
    WHERE s_user_id=$user_id
    AND d_key_id = $score_key				 
    ORDER BY score DESC)a
    GROUP BY d_key_id

Any help would be greatly appreciated!

[mousey182]

Im tyring to select the d_key_id from that last statement, however it just throws up errors. I used the syntax from the MySQL website, and still doesnt work, anyone got any ideas why?
Im guessing its because the AVG(score) is from an additional Select statement, but dont think this should cause any problems!

[stephen84s]

Whats the error MySQl is throwing ?

I don't think the following should make a difference but have you tried like this :

SELECT a.d_key_id, AVG(a.score) as 'avg' FROM
   (SELECT d_key_id, frequency, weighting,(frequency*weighting) as 'score'
    FROM users_to_keywords
    WHERE s_user_id=$user_id
    AND d_key_id = $score_key				 
    ORDER BY score DESC)a
    GROUP BY d_key_id

[mousey182]

Hi Steven, Thanks for the reply!
It was throwing up an error saying that the result wasnt a valid mysql_query result, however it appears to be working now?!?! Im very confused, but not complaining!
Thanks for your help!