Customer Purchase Summary

A GROUP BY customer_id collapses every purchase row for one customer into a single output row, and the aggregate functions run over the set of grouped rows — COUNT(DISTINCT product) deduplicates while it counts, and GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',') deduplicates, sorts, and stitches the surviving values into one comma-joined string, all inside the same grouping pass.

The problem

Given a purchases table where a customer can buy the same product more than once, produce one row per customer showing how many distinct products they bought and an alphabetised, comma-separated list of those product names.

-- purchases
 purchase_id | customer_id | product
-------------+-------------+----------
      1      |     101     | Mouse
      2      |     101     | Keyboard
      3      |     101     | Mouse      -- repeat
      4      |     102     | Monitor
      5      |     101     | Webcam
      6      |     102     | Monitor    -- repeat

The query (MySQL)

SELECT
    customer_id,
    COUNT(DISTINCT product) AS distinct_products,
    GROUP_CONCAT(DISTINCT product ORDER BY product SEPARATOR ',') AS product_list
FROM purchases
GROUP BY customer_id
ORDER BY customer_id;

The two aggregates are independent passes over the same group: one returns a number, the other returns a string. The DISTINCT, ORDER BY, and SEPARATOR clauses all live inside the GROUP_CONCAT parentheses — they shape that one aggregate's input, not the outer query.

Step-by-step trace for customer 101

Customer 101 has four purchase rows: Mouse, Keyboard, Mouse, Webcam. Watch what each clause does to that multiset, in order.

In parallel, COUNT(DISTINCT product) sees the same step-2 set {Keyboard, Mouse, Webcam} and returns 3 — not 4, because the duplicate Mouse was collapsed before counting.

Full output

Customer 102 bought Monitor twice; DISTINCT collapses it to one, so the count is 1 and the list is the single value.

Why the naive version is wrong

The instinctive write is COUNT(product) and a bare GROUP_CONCAT(product). Both fail on real data:

• COUNT(product) counts rows, so customer 101 reports 4 (the repeated Mouse is counted twice). You wanted distinct products, so you need COUNT(DISTINCT product).
• Bare GROUP_CONCAT(product) emits Mouse,Keyboard,Mouse,Webcam — duplicated and in arbitrary engine order. You must put DISTINCT and ORDER BY inside the call to get a stable, deduplicated list.

Portability: this is MySQL-specific

That DISTINCT ... ORDER BY ... SEPARATOR form is MySQL/MariaDB syntax. The standard-SQL equivalent (PostgreSQL, modern SQL Server, Oracle) is STRING_AGG, and its grammar differs:

-- PostgreSQL
SELECT
    customer_id,
    COUNT(DISTINCT product) AS distinct_products,
    STRING_AGG(DISTINCT product, ',' ORDER BY product) AS product_list
FROM purchases
GROUP BY customer_id
ORDER BY customer_id;

The separator is a positional argument, not a SEPARATOR keyword. PostgreSQL allows DISTINCT together with ORDER BY only when the ORDER BY expression matches the aggregated expression — order by product while aggregating product is fine; order by some other column would raise "in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list". SQL Server's STRING_AGG uses WITHIN GROUP (ORDER BY product) and has no DISTINCT support at all — you must pre-deduplicate in a subquery or CTE.

Pitfalls

• Silent truncation. MySQL caps GROUP_CONCAT output at group_concat_max_len (default 1024 bytes). A customer with thousands of products gets a truncated string with no error — the row still returns, just incomplete. Raise it with SET SESSION group_concat_max_len = 1000000; for wide aggregations.
• NULLs vanish, not error. GROUP_CONCAT skips NULL products entirely, so a row with all-NULL products yields NULL for the whole list, and COUNT(DISTINCT product) also ignores NULLs — a customer with only NULL products counts 0. If absence matters, COALESCE first.
• DISTINCT and ORDER BY are not free. Both force MySQL to materialise and sort each group's values; on large groups this spills to a temporary table on disk. It is a per-group sort, not a global one.
• Separator collisions. If a product name itself contains a comma ("Cable, USB-C"), a comma separator makes the result unparseable downstream. Pick a separator that cannot appear in the data (e.g. a tab or '||'), or return JSON via JSON_ARRAYAGG.
• Don't double-aggregate. Wrapping COUNT around GROUP_CONCAT or vice-versa is a parse error — they are sibling aggregates over the same group, not nested.

Takeaways

• COUNT(DISTINCT col) dedupes before counting; bare COUNT(col) counts rows — they diverge the moment a value repeats.
• Inside GROUP_CONCAT, the order is fixed: DISTINCT dedupes, then ORDER BY sorts, then SEPARATOR joins — all scoped to one group.
• String aggregation is the least portable common aggregate: MySQL GROUP_CONCAT vs Postgres/standard STRING_AGG vs SQL Server's no-DISTINCT WITHIN GROUP. Know which engine you target.
• Watch the silent failure modes — length truncation and dropped NULLs return a wrong answer with no error.

Re-authored and deepened for this guide. Mechanism and clause-ordering semantics verified against the MySQL 8.0 Reference Manual (12.20 Aggregate Functions, GROUP_CONCAT and group_concat_max_len), the PostgreSQL 16 documentation (9.21 Aggregate Functions, STRING_AGG with DISTINCT/ORDER BY constraints), and the Microsoft SQL Server STRING_AGG / WITHIN GROUP reference. Worked example and failure modes authored for this page.