2

I have following Table

| id | a | b | c |
-------------------
| 1  | x | y | r |
| 2  | x | y | q |
| 3  | x | y | f |

Result should be 

| a | b | c |
-------------
| x | y | r,q,f |

Tried with

select a, b, group_concat(c) from table;

but it says:

Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'table.table'; this is incompatible with sql_mode=only_full_group_by

Hope you can help me, thanks!

GMB
  • 216,147
  • 25
  • 84
  • 135
Fr34k
  • 31
  • 1
  • follow this https://stackoverflow.com/questions/23921117/disable-only-full-group-by – Abdullah Al Shakib Feb 07 '19 at 19:09
  • The error said it all "Error Code: 1140. In aggregated query **without GROUP BY**" The error is right you can't mix non aggregate columns with a aggregate (GROUP_CONCAT) colum it is not ANSI SQL.. But notice the error saids **without GROUP BY**, try adding `GROUP BY a, b` pretty sure that would give you the correct results. – Raymond Nijland Feb 07 '19 at 19:09
  • @AbdullahAlShakib No disabling that sql_mode is a bad idea.. – Raymond Nijland Feb 07 '19 at 19:10
  • i think, here is rather an issue with the query, than a duplicate ... the query would look better, if: `select a, b, group_concat(c) from table GROUP BY a, b;` – xerx593 Feb 08 '19 at 01:24

3 Answers3

2

GROUP_CONCAT() is an aggregate function, which means that it requires you to group your results.

This query returns your expected results :

SELECT a, b, GROUP_CONCAT(c ORDER BY id) c
FROM mytable
GROUP BY a, b

Notes :

  • , is the default separator for GROUP_CONCAT
  • GROUP_CONCAT supports ORDER BY

Demo on DB Fiddle :

a  | b  | c
:- | :- | :----
x  | y  | r,q,f
GMB
  • 216,147
  • 25
  • 84
  • 135
1

Try something like this

Select GROUP_CONCAT(DISTINCT c SEPARATOR ',') from table
Group By a,b
Adas
  • 404
  • 2
  • 19
0

Try:

SELECT a, b, GROUP_CONCAT(c) c FROM table GROUP BY a,b

Vinesh Shakelli
  • 11
  • 2