CODEWITHSUNDEEP
mysqlsqlrelational-database
Ashley Hunt
Ashley Hunt

Reputation: 47

SQL concat IDs of rows after group by

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.

Upvotes: 0

Views: 578

Answers (1)

Jonas Metzler
Jonas Metzler

Reputation: 5975

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

Upvotes: 2

Related Questions