order by on calculated field that also has group_concat in mySql

Question

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

person
- PersonId( PK)
- PersonName

tests
- 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

SELECT GROUP_CONCAT( CASE  
    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

What is your expected result? you want it ordered by scores? — d-_-b, Dec 09 '12 at 05:22
@kermit yup.. i am using group_concat to get the result as a comma separated list.. — cartic natraj, Dec 09 '12 at 07: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 09 '12 at 07:02

score 0 · Answer 1 · answered Dec 09 '12 at 05:34

0

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
INNER JOIN
(
SELECT personID, Avg(test1score) AS AvgOftest1score, Avg(test2score) AS AvgOftest2score
FROM tests
GROUP BY personID
) p
ON person.personID = p.personID
ORDER BY p.AvgOftest1score, p.AvgOftest2score;

answered Dec 09 '12 at 05:34

kermit

1,621
1
14
14

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 09 '12 at 06:59

order by on calculated field that also has group_concat in mySql

1 Answers1