GROUP_CONCAT with ordering

Combine rows into text

Lesson 8 of 20 · 9 min · Module 2: Aggregation that answers real questions

Order confirmations, tags, and summaries often need a list in one cell: "Ceramic Mug x2, Green Tea x1". GROUP_CONCAT joins the values of a group into a single string.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Aggregate text values with GROUP_CONCAT
Order values inside an aggregate
Build strings with ||

The idea

GROUP_CONCAT(value, ', ') concatenates the group’s values with a separator. Add ORDER BY inside the parentheses to control the order of the list. The || operator joins text inside each value.

SELECT category, GROUP_CONCAT(name, ', ' ORDER BY name) AS products
FROM products
GROUP BY category;

01One string per group

GROUP_CONCAT is to text what SUM is to numbers: it combines all values of a group into one. The second argument is the separator.

02Order inside the aggregate

Without ORDER BY inside GROUP_CONCAT, the list follows whatever order the database read the rows in, which can change. ORDER BY p.name makes the result stable.

03Concatenate with ||

'Tea' || ' x' || 2 produces 'Tea x2'. Numbers are converted to text automatically.

SELECT 'Tea' || ' x' || 2;

Output

Tea x2

Your turn

For every order that is not cancelled, return id, the customer’s name, and items: the products in that order formatted as Name xQuantity, separated by , and sorted by product name. Sort by order id.

What the checks look for

Lists the items of every order that was not cancelled

Try it in the editor →

Take this with you

GROUP_CONCAT turns a group of rows into a readable list.

Go a little further

Return each category with a list of its products and their stock, such as "Notebook (100)".