All geek questions in one place

One to Many Groups in MySQL

I have a problem. I have the following tables in MySQL:

+---------+ +-----------+ +---------+ | USER | | USER_LANG | | LANG | | id_user | | id_user | | id_lang | | name | | id_lang | | name | | | | years | | | +---------+ +-----------+ +---------+

and I have the following information:

 +--------------------------------+ | ID_USER | ID_LANG | YEARS | | 1 | 5 | 1 | | 1 | 6 | 2 | | 2 | 5 | 9 | | 2 | 6 | 3 | | 3 | 5 | 7 | +--------------------------------+

I need to achieve to group all users who speak the same language group and summarize the years. For example, I need to get:

 +----------------------+ | LANGS | SUM_YEARS | | 5;6 | 15 | | 5 | 7 | +----------------------+

This is very strange, but I need it. I tried with GROUP_CONCAT but did not work.

I hope they can help me.

Thanks in advance.

+4
source share
1 answer

This is an unusual query, but it should work with GROUP_CONCAT() inside the subquery:

 /* Outer query groups the concatentated langs and sums years */ SELECT LANGS, SUM(YEARS) AS TOTAL_YEARS, /* Edit: bonus list of users speaking the languages */ GROUP_CONCAT(ID_USER) AS USER_GROUP FROM ( /* Subquery gets group concat of languages & sum per user */ SELECT ID_USER, GROUP_CONCAT(ID_LANG) AS LANGS, SUM(YEARS) AS YEARS FROM USER_LANG GROUP BY ID_USER ) USER_SUB GROUP BY LANGS
+3
source

Source: https://habr.com/ru/post/1397048/

More articles:


All Articles