MySQL GROUP_CONCAT in another GROUP_CONCAT

Question

Is it possible at all? What I want to get is something like this:

+-------------------------------+
| data 1 (another1, another2);  |
| data 2 (another1 ..... )      |
+-------------------------------+

From table something like this:

--------------------------------+
data1 | another1 | foreign key1 |
--------------------------------+
data1 | another2 | foreign key1 |
--------------------------------+
data2 | another1 | foreign key1 |
--------------------------------+

I use this code

GROUP_CONCAT(DISTINCT CONCAT(data, ' (',  another, ')') SEPARATOR ';\r\n ') as InOneCell

and got this output:

+-------------------------------+
| data 1 (another1);            |
| data 1 (another2);            |
| data 2 (another1); ...        |
+-------------------------------+

Tried this:

GROUP_CONCAT(DISTINCT CONCAT(data, ' (',  GROUP_CONCAT(another SEPARATOR ', '), ')') SEPARATOR ';\r\n ') as InOneCell

and got error "Invalid use of group function", which is really shady -_- I do group by - foreing key due to complex query (lower).

What I do wrong or it is impossible on only SQL?

Here the full query:

SELECT  
s.name, 
s.surname, 
gr.number AS 'group', 
s.bitrh_date, 
s.email, 
s.registration_ip,
s.registration_time,
GROUP_CONCAT(DISTINCT CONCAT(dis.code, ' ', dis.title, ' (',  GROUP_CONCAT(DISTINCT g.mark SEPARATOR ', '), ')') SEPARATOR ';\r\n ') as learning,
ROUND(AVG(g.mark), 2) AS average,
gr.semester,
ref.text AS reference

FROM t_students s
LEFT JOIN t_grades g
    ON s.id = g.student
LEFT JOIN t_groups gr
    ON s.group_id = gr.id
LEFT JOIN t_references ref
    ON ref.author = s.id
LEFT JOIN t_program prog
    ON gr.id = prog.group_id
LEFT JOIN t_disciplines dis
    ON prog.discipline_id = dis.code
GROUP BY s.id
ORDER BY s.registration_time DESC

Answer 1

You should use group by (and should be enough a single group_concat)

select CONCAT(data, ' (',  GROUP_CONCAT(another SEPARATOR ', '), ');') as InOneCell
from my_table  
group by data 

or you could use a subquery

select data, group_concat(my_group)
from (
  select data, group_concat(another) my_group 
  group by data
) t 
group by data 

in your case You could use a subquery eg:

  SELECT  
  s.name, 
  s.surname, 
  gr.number AS 'group', 
  s.bitrh_date, 
  s.email, 
  s.registration_ip,
  s.registration_time,
  GROUP_CONCAT(DISTINCT CONCAT(dis.code, ' ', dis.title, ' (', t.learning, ')') SEPARATOR ';\r\n ') as learning,
  t.average,
  gr.semester,
  ref.text AS reference

  FROM t_students s
  LEFT JOIN (SELECT
    student, 
    GROUP_CONCAT(DISTINCT mark SEPARATOR ', ') learning,
    ROUND(AVG(mark), 2) AS average
    from t_grades
    group by student
    ) t on t.student = s.id
  LEFT JOIN t_groups gr
      ON s.group_id = gr.id
  LEFT JOIN t_references ref
      ON ref.author = s.id
  LEFT JOIN t_program prog
      ON gr.id = prog.group_id
  LEFT JOIN t_disciplines dis
      ON prog.discipline_id = dis.code

  GROUP BY s.id
  ORDER BY s.registration_time DESC

Answer 2

I guess you can try this, just only use GROUP_CONCAT once, and add GROUP BY

SELECT CONCAT(data,'(',GROUP_CONCAT(DISTINCT another),');')
FROM T
group by data

sqlfiddle