SQL concat IDs of rows after group by

Question

I'm trying to output a query that has a count of a group by but also, specifies the list of IDs in that row.

This is what I have so far:

SELECT
    title,
    period,
    COUNT(*)
FROM
    table
GROUP BY
    title, period

Example database

Title	Period	ID
Title2	MONTH	321
Title1	DAY	789
Title1	DAY	123
Title1	MONTH	123

Output

Title	Period	COUNT(*)
Title2	MONTH	1
Title1	DAY	2
Title1	MONTH	1

But I would like the output to be something like:

Title	Period	COUNT(*)	Who?
Title2	MONTH	1	321
Title1	DAY	2	123, 789
Title1	MONTH	1	123

What do I need to add to my query to get this output? I've tried to use an SELF JOIN and a SELECT JOIN, but I cannot quite get the syntax right.

So you are looking for an aggregate string concatenation function, like STRING_AGGR in SQLServer or LIST in Firebird, but then for MySQL. — GWR, Dec 01 '22 at 12:13
When pinned down & clearly expressed this will be a faq. (Clearly.) Please before considering posting: Pin down code issues via [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [research effort](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] — philipxy, Dec 02 '22 at 06:26
Does this answer your question? [Can I concatenate multiple MySQL rows into one field?](https://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field) — philipxy, Dec 02 '22 at 06:30

Jonas Metzler · Answer 1 · 2022-12-01T12:21:58.000

We can use GROUP_CONCAT:

SELECT
    title,
    period,
    COUNT(*),
    GROUP_CONCAT(id ORDER BY id SEPARATOR ', ') AS Who
FROM
    yourtable
GROUP BY
    title, period
ORDER BY title DESC;

Note: I don't know if the two ORDER BY clauses are necessary for you. I just added them to produce exactly your outcome. Remove them if not needed.

You can also remove the part SEPARATOR ', ' if you don't require spaces after the commas.

Try out: db<>fiddle

Here the documentation: documentation

SQL concat IDs of rows after group by

1 Answers1