How to implement GROUP_CONCAT

Question

here's my sql statement

SELECT ut.*, GROUP_CONCAT(ut.bet_group_id)
FROM users_transaction AS ut
WHERE ut.user_id = 3
GROUP BY ut.user_id
ORDER BY ut.id DESC;

my is goal is to group_concat the bet_group_id by user_id, but when i execute my query above, i get this following error Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'p2p_trade.ut.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

and here's my sample table

  id   user_id   bet_group_id 
   1      3         2            
   2      3         1            
   3      3         2           
   4      3         null

expected output will be

  id   user_id   bet_group_id  type_id
   1      3         2,2         
   2      3         1           
   4      3         null

i don't understand the logic to merge id 1,3 to 1 and type_id 1,3 to 1 — Hirumina, Aug 20 '20 at 04:41
Your error message has backticks, but the query doesn't. That error message must be for a different query. Show the real query. — Barmar, Aug 20 '20 at 05:38
Edit the question, the backticks are treated as markdown in the comment. — Barmar, Aug 20 '20 at 05:41
Describe grouping logic. Now it looks like grouping by `(user_id, bet_group_id)` with taking minimal `id` and `type_id` values for a group. And your `ORDER BY ut.id DESC` contradicts desired output where ordering direction is ASC. — Akina, Aug 20 '20 at 05:41
@Barmar i didn't include the deleted_at, it's not important. — mrwick2000, Aug 20 '20 at 05:42
Your expected output has multiple rows for the same `user_id`. That's not possible with `GROUP BY user_id`. — Barmar, Aug 20 '20 at 05:43
When I run your query I get a syntax error near `GROUP BY` because you have the clauses in the wrong order. — Barmar, Aug 20 '20 at 05:44
If it's not important, don't put it in the query -- it causes an error when I try to run your query at sqlfiddle with the sample data. — Barmar, Aug 20 '20 at 05:45
Put `GROUP BY` before `ORDER BY`, then you won't get a syntax error. — Barmar, Aug 20 '20 at 05:46
You still haven't edited the question to show the query that actually caused that error message. — Barmar, Aug 20 '20 at 05:50

score 1 · Accepted Answer · answered Aug 20 '20 at 05:49

1

The syntax error is because GROUP BY has to be before ORDER BY, although the error message is not the same as the one you show (it should say near "GROUP BY").

In order to get a separate row for each bet_group_id, you need to incluide that column in the GROUP BY clause.

And to get the rows in the order you want, don't use DESC.

SELECT ut.*, GROUP_CONCAT(ut.bet_group_id)
FROM users_transaction AS ut
WHERE ut.user_id = 3
GROUP BY ut.user_id, ut.bet_group_id
ORDER BY ut.id

DEMO

answered Aug 20 '20 at 05:49

Barmar

741,623
53
500
612

i think there's no wrong with this query, it's now producing diff error. – mrwick2000 Aug 20 '20 at 06:00
Are you getting an error about `ONLY_FULL_GROUP_BY`? – Barmar Aug 20 '20 at 06:02
See https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql – Barmar Aug 20 '20 at 06:02

How to implement GROUP_CONCAT

1 Answers1