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
– GWR, Commented Dec 1, 2022 at 12:13
When pinned down & clearly expressed this will be a faq. (Clearly.) Please before considering posting: Pin down code issues via minimal reproducible example. 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 Stack Overflow & Meta Stack Exchange. research effort How to Ask Help center — philipxy
– philipxy, Commented Dec 2, 2022 at 6:26
Does this answer your question? Can I concatenate multiple MySQL rows into one field? — philipxy
– philipxy, Commented Dec 2, 2022 at 6:30

Jonas Metzler · Accepted Answer · 2022-12-01 12:21:58Z

2

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

edited Dec 1, 2022 at 12:21

answered Dec 1, 2022 at 12:14

Jonas Metzler

6,8363 gold badges9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL concat IDs of rows after group by

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related