Collapsing a group into a string in SQL

Sometimes the answer is not a number but the list itself.

SELECT
    o.id,
    COUNT(*) AS lines,
    GROUP_CONCAT(
        i.sku,
        ', '
        ORDER BY i.sku
    ) AS skus
FROM orders AS o
JOIN order_items AS i
    ON i.order_id = o.id
GROUP BY o.id;

How it works

1. GROUP_CONCAT joins a group's values with a separator.
2. ORDER BY inside the call makes the output stable run to run.
3. DISTINCT inside an aggregate drops repeats before combining.

Keywords and builtins used here

• AS
• BY
• COUNT
• FROM
• GROUP
• JOIN
• ON
• ORDER
• SELECT

The run, in numbers

Lines

12

Characters to type

157

Tokens

49

Three-star pace

85 tpm

At the three-star pace of 85 tokens a minute, this run takes about 35 seconds.

Type this snippet

Step 4 of 4 in Grouping, step 8 of 22 in Joins & aggregation.

← Previous Next →