Take the 2-minute tour ×

Here is a simplified version of what I am trying to accomplish. Say I have a person table and a tests table. Each test has 2 scores associated with it and each person can have 0,1 or many tests

- PersonId( PK)
- PersonName

- testId (PK)
- PersonId (FK)
- test1Score
- test2Score

Im trying to get a result set that gives me a sorted list of persons and average testScores. This is what Im doing

    WHEN( tests.test1Score > 0 )   
    THEN( ( tests.test1Score + tests.test2Score)/2 )  
    ELSE( 99999999 ) END) as averageScore, person.PersonName  
FROM person  
JOIN tests  
    ON person.personId = tests.personId   
GROUP BY person.personId   
ORDER BY averageScore

What is happening is that group_concat returns a string and doing my order by results in something like this

personName1 | 5,6
personName2 | 51,60
personName3 | 6, 60

I know I have to order by the individual calculated field first and then do order by the group_concatenated value, but am not sure how to do it..

any help would be appreciated

share|improve this question
Do you have to use group_concat to accomplish this? –  kermit Dec 9 '12 at 5:20
What is your expected result? you want it ordered by scores? –  d-_-b Dec 9 '12 at 5:22
stackoverflow.com/a/5085334/1166285 –  d-_-b Dec 9 '12 at 5:23
@kermit yup.. i am using group_concat to get the result as a comma separated list.. –  cartic natraj Dec 9 '12 at 7:00
@iight yup.. ordered by scores. thanks for that example, but it shows you can order by using existing fields.. in my case, I would need to order by a calculated field.. –  cartic natraj Dec 9 '12 at 7:02

1 Answer 1

You could do this with an aggregate subquery, and group on whatever fields you like, instead of on the concatenated string. Example using your fields:

SELECT person.personID, person.personName, p.AvgOftest1score, p.AvgOftest2score
FROM person
SELECT personID, Avg(test1score) AS AvgOftest1score, Avg(test2score) AS AvgOftest2score
FROM tests
) p
ON person.personID = p.personID
ORDER BY p.AvgOftest1score, p.AvgOftest2score;
share|improve this answer
agreed.. but i need the results to be in the format mentioned above.. with your example, AvgOftest1score and AvgOftest2score are 2 distinct fields.. I would like them to be comma separated (hence the use of GROUP_CONCAT). –  cartic natraj Dec 9 '12 at 6:59

Your Answer


