Lists in One Cell
GROUP_CONCAT.
Sometimes you want a list, not a number. Which cities do our customers live in, for each country? Group by would give one row per city. But Helen wants one row per country, with the cities listed together.

- A list, not a number
- Cities per country
- One row each
GROUP_CONCAT
Group concat is an aggregate function, like count. But instead of counting, it joins the values together with commas. Distinct removes repeats. Run it. Each country, with all its cities in one cell.
SELECT country,
GROUP_CONCAT(DISTINCT city) AS cities
FROM customers
WHERE city IS NOT NULL
GROUP BY country;โโโโโโโโโโโโโฌโโโโโโโโโโโโโโโโโโโโโโโโโโ โ country โ cities โ โโโโโโโโโโโโโผโโโโโโโโโโโโโโโโโโโโโโโโโโค โ Australia โ Melbourne,Sydney โ โ Canada โ Toronto,Vancouver โ โ Germany โ Berlin โ โ Ireland โ Dublin โ โ UK โ Manchester,Leeds,London โ โ USA โ Chicago,New York,Austin โ โโโโโโโโโโโโโดโโโโโโโโโโโโโโโโโโโโโโโโโโ
Choosing the separator
You can choose the separator, as a second argument. Here we use a vertical bar, to list each author's books. Run it. Kai Lund wrote all three fantasy books. In Postgres and SQL Server, the same function is called string ag.
SELECT author,
COUNT(*) AS books,
GROUP_CONCAT(title, ' | ') AS titles
FROM books
WHERE genre IN ('Fantasy', 'Mystery')
GROUP BY author;โโโโโโโโโโโโโโฌโโโโโโโโฌโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ โ author โ books โ titles โ โโโโโโโโโโโโโโผโโโโโโโโผโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโค โ Kai Lund โ 3 โ Dragons of Ember Bay | Crown of Ash | The Glass Forest โ โ Mira Hale โ 3 โ The Silent Orchard | The Last Lighthouse | Murder at Mill Lane โ โ Nora Wells โ 1 โ The Painted Door โ โโโโโโโโโโโโโโดโโโโโโโโดโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
Summary
Group concat joins a group's values into one cell. Handy for reports, and emails. Next, a query that calls itself.
Key takeaways- GROUP_CONCAT(col)
- Choose a separator
- STRING_AGG elsewhere