Question 21608 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql collecting the name based on favorite and sorting the collected name with subsort

Prevously I asked a question here, but after re-read it, I realised it’s more complicated so I’m asking a new question here.

I have following table: how to sort the table based on collecting the name from the same favorite, and also sorting the name based on their sum of score?

table: student
| FAVORITE | NAME   | score1 | score2 |  
|----------|--------|--------|--------|  
| math     | john   |     98 |     97 |  
| chem     | ryan   |     97 |    100 |  
| history  | yinyin |     80 |     95 |  
| math     | oda    |     99 |    100 |  
| history  | ed     |     67 |     85 |

expected result table after querying collecting name, and also sorting name:

table:favorite
| FAVORITE | NAME       |
|----------|------------|
| math     | john, oda  |
| chem     | ryan       |
| history  | ed, yinyin |

i’ve tried this:

SELECT FAVORITE, GROUP_CONCAT(
    select name from (
        select name, sum(score1 + score2) as total_score
        from student
        group by name
        order by total_score asc
    ) as T
) AS NAME
FROM student
GROUP BY FAVORITE;

please help, thank you.

Tags:

2

Answers


  1. 0

    Based on Your Previous question’s accepted answer., just add order by (score1+score2).

    SELECT FAVORITE, GROUP_CONCAT(NAME order by (`score1`+`score2`)) AS NAME
FROM yourTable
GROUP BY FAVORITE;
    Login or Signup to reply.
  2. 0

    You just need to provide the required value to order by using a derived table first, then specify the order in the group_concat syntax:

    select FAVORITE, group_concat(NAME order by tot separator ', ') Name
from (
  select *, score1 + score2 tot
  from t
)t
group by FAVORITE;

    See Demo Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search