CODEWITHSUNDEEP
mysqlgroup-by
Jonathan Toledo
Jonathan Toledo

Reputation: 359

group by one to many 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   |
+--------------------------------+

What I need to achieve is to group all users who speak exactly the same group of languages ​​and sum the years. For example, I need to get is:

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

It is very strange, but I need this. I tried with GROUP_CONCAT but didn't work.

Hopefully they can help me.

Thanks in advance.

Upvotes: 4

Views: 1163

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270775

That's an unusual request, but it should work out with the GROUP_CONCAT() inside a 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

Upvotes: 3

Related Questions