SQL (PostgreSQL, MySQL, SQLite) Module 4 – Aggregates and GROUP BY

String aggregation: GROUP_CONCAT and STRING_AGG

Join the values of each group into one string with group_concat and string_agg in SQL, choose their order and separator, and avoid each engine's traps.

Intermediate
15 minutes
Examples run with sql.js 1.14.2
By MySmartCoPilot

What you will learn

Join the values of each group into one string in a chosen order
Use each engine's function and know its limits
Choose a JSON array instead of a string when the list is for a program

Before you start

GROUP BY in SQL: one row per group

On this page

Sometimes the summary of a group is not a number but a list: the products on each order, the students in each course, the cities a delivery rider covered. String aggregation joins the values of a group into one piece of text, with a separator between them. All three engines of this track have it, under different names: group_concat in SQLite and MySQL, string_agg in PostgreSQL, and both names in recent SQLite.

You can run every example here, in SQLite 3.49.1 as bundled by sql.js 1.14.2; a table at the end compares the three engines.

One string per group

The products of each order as one string SQL · list_per_order.sql

-- One row per product on an order.
CREATE TABLE order_items (order_id INTEGER, product TEXT);
INSERT INTO order_items VALUES
  (101, 'Toor dal 1 kg'), (101, 'Basmati rice 5 kg'), (101, 'Atta 5 kg'),
  (102, 'Milk 1 l'), (102, 'Bread'),
  (103, 'Paneer 200 g');

-- One string per order: the products joined with ', '.
SELECT order_id,
       group_concat(product, ', ') AS products
FROM order_items
GROUP BY order_id
ORDER BY order_id;

Output

┌──────────┬─────────────────────────────────────────────┐
│ order_id │                  products                   │
├──────────┼─────────────────────────────────────────────┤
│ 101      │ Toor dal 1 kg, Basmati rice 5 kg, Atta 5 kg │
│ 102      │ Milk 1 l, Bread                             │
│ 103      │ Paneer 200 g                                │
└──────────┴─────────────────────────────────────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < list_per_order.sql

group_concat(product, ', ') takes the products of each group and joins them, with ', ' between neighbours and nothing after the last one. Without the second argument the separator is a bare comma.

Warning

MySQL reads a second argument differently. Its GROUP_CONCAT joins all its arguments into each value, so GROUP_CONCAT(product, ', ') glues ', ' onto every product and still separates them with plain commas: three products come out as dal, ,rice, ,tea, . In MySQL the separator follows the keyword SEPARATOR, as the table at the end of this lesson shows.

Look at the order of the products in order 101: it is the order the rows happened to be read, not alphabetical. Without an explicit order, the order of the values is not defined, and it can change when the table grows, when an index is added or when the engine is upgraded.

Choosing the order

Write the order inside the call, after the last argument:

An ordered list SQL · ordered_list.sql

-- One row per product on an order.
CREATE TABLE order_items (order_id INTEGER, product TEXT);
INSERT INTO order_items VALUES
  (101, 'Toor dal 1 kg'), (101, 'Basmati rice 5 kg'), (101, 'Atta 5 kg'),
  (102, 'Milk 1 l'), (102, 'Bread'),
  (103, 'Paneer 200 g');

-- ORDER BY inside the call sorts the values before they are joined.
SELECT order_id,
       group_concat(product, ', ' ORDER BY product) AS products
FROM order_items
GROUP BY order_id
ORDER BY order_id;

Output

┌──────────┬─────────────────────────────────────────────┐
│ order_id │                  products                   │
├──────────┼─────────────────────────────────────────────┤
│ 101      │ Atta 5 kg, Basmati rice 5 kg, Toor dal 1 kg │
│ 102      │ Bread, Milk 1 l                             │
│ 103      │ Paneer 200 g                                │
└──────────┴─────────────────────────────────────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < ordered_list.sql

ORDER BY inside the call sorts the values of each group before they are joined; it can be any expression and can say DESC. It has nothing to do with the ORDER BY at the end of the query, which sorts the result rows. Here both appear: the inner one sorts the products, the outer one sorts the orders.

SQLite also accepts string_agg(value, separator), the name PostgreSQL uses. It is the same function, but its separator is required:

Students per elective course, alphabetically SQL · electives.sql

-- Who chose which elective course: one row per student and course.
CREATE TABLE choices (student TEXT, course TEXT);
INSERT INTO choices VALUES
  ('Kiran', 'Data mining'), ('Aarav', 'Compilers'), ('Harini', 'Data mining'),
  ('Faisal', 'Compilers'), ('Chitra', 'Cryptography'), ('Dev', 'Data mining'),
  ('Bhavna', 'Compilers'), ('Esha', 'Cryptography');

-- string_agg is SQLite's second name for group_concat with a separator, as in PostgreSQL.
SELECT course,
       count(*) AS students,
       string_agg(student, ', ' ORDER BY student) AS names
FROM choices
GROUP BY course
ORDER BY students DESC, course;

Output

┌──────────────┬──────────┬───────────────────────┐
│    course    │ students │         names         │
├──────────────┼──────────┼───────────────────────┤
│ Compilers    │ 3        │ Aarav, Bhavna, Faisal │
│ Data mining  │ 3        │ Dev, Harini, Kiran    │
│ Cryptography │ 2        │ Chitra, Esha          │
└──────────────┴──────────┴───────────────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < electives.sql

Version note

ORDER BY inside an aggregate call and the name string_agg arrived in SQLite 3.44.0. The browser’s SQLite 3.49.1 runs these examples; an older SQLite, such as one built into an older program, rejects them.

Duplicates and DISTINCT

A rider who delivers to the same city twice would have it listed twice. DISTINCT removes repeats, but in SQLite only for an aggregate with a single argument, so it cannot be combined with your own separator:

DISTINCT with a separator SQL · distinct_separator.sql

-- The cities each rider delivered to; Joseph went to Kochi twice.
CREATE TABLE deliveries (rider TEXT, city TEXT);
INSERT INTO deliveries VALUES
  ('Joseph', 'Kochi'), ('Joseph', 'Thrissur'), ('Joseph', 'Kochi'),
  ('Lalitha', 'Madurai'), ('Lalitha', 'Chennai');

-- DISTINCT together with a separator: SQLite refuses.
SELECT rider, group_concat(DISTINCT city, ', ') AS cities
FROM deliveries
GROUP BY rider;

Output (exit status 1)

Printed as an error (standard error)

Parse error near line 8: DISTINCT aggregates must have exactly one argument

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < distinct_separator.sql

group_concat(DISTINCT city) alone works and uses the comma separator. To keep ', ', remove the duplicates first, in a subquery, and join what is left:

Remove duplicates, then join SQL · distinct_subquery.sql

CREATE TABLE deliveries (rider TEXT, city TEXT);
INSERT INTO deliveries VALUES
  ('Joseph', 'Kochi'), ('Joseph', 'Thrissur'), ('Joseph', 'Kochi'),
  ('Lalitha', 'Madurai'), ('Lalitha', 'Chennai');

-- Remove the duplicates first, in a subquery; then join what is left.
SELECT rider,
       group_concat(city, ', ' ORDER BY city) AS cities
FROM (SELECT DISTINCT rider, city FROM deliveries)
GROUP BY rider
ORDER BY rider;

Output

┌─────────┬──────────────────┐
│  rider  │      cities      │
├─────────┼──────────────────┤
│ Joseph  │ Kochi, Thrissur  │
│ Lalitha │ Chennai, Madurai │
└─────────┴──────────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < distinct_subquery.sql

PostgreSQL and MySQL accept DISTINCT with a separator directly, as the table at the end shows.

NULLs and empty groups

String aggregation skips NULLs, like the other aggregates. When a group has nothing but NULLs, the result is NULL, not an empty string:

NULL values and an all-NULL group SQL · nulls_and_empty.sql

-- Order 202 has a line whose product is not known yet; order 203 has only such lines.
CREATE TABLE order_items (order_id INTEGER, product TEXT);
INSERT INTO order_items VALUES
  (201, 'Curd 500 g'), (201, 'Ghee 500 ml'),
  (202, 'Jaggery 1 kg'), (202, NULL),
  (203, NULL);

SELECT order_id,
       group_concat(product, ', ')                AS products,
       coalesce(group_concat(product, ', '), '-') AS shown
FROM order_items
GROUP BY order_id
ORDER BY order_id;

Output

┌──────────┬─────────────────────────┬─────────────────────────┐
│ order_id │        products         │          shown          │
├──────────┼─────────────────────────┼─────────────────────────┤
│ 201      │ Curd 500 g, Ghee 500 ml │ Curd 500 g, Ghee 500 ml │
│ 202      │ Jaggery 1 kg            │ Jaggery 1 kg            │
│ 203      │                         │ -                       │
└──────────┴─────────────────────────┴─────────────────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < nulls_and_empty.sql

Order 202’s unknown product is left out, so its list is just Jaggery 1 kg. Order 203 has no known product at all and its list is NULL (the empty cell). If a report should show a placeholder, wrap the call in coalesce, as the shown column does.

A common mistake: ORDER BY in the wrong place

With two arguments, it is easy to put ORDER BY before the separator:

ORDER BY before the separator SQL · misplaced_order_by.sql

CREATE TABLE items (order_id INTEGER, product TEXT);
INSERT INTO items VALUES (1, 'tea'), (1, 'rice'), (1, 'dal');

-- The ORDER BY is inside the call, but before the separator.
SELECT group_concat(product ORDER BY product, ', ') AS products
FROM items;

Output

┌──────────────┐
│   products   │
├──────────────┤
│ dal,rice,tea │
└──────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < misplaced_order_by.sql

The query runs, and the products are sorted, but the separator is a bare comma: the engine read ORDER BY product, ', ' as an ordering by two keys, the product and the constant text ', ', and called group_concat with one argument. MySQL does the same with GROUP_CONCAT. PostgreSQL stops with an error instead, because it has no one-argument string_agg; its manual uses this exact mistake as a warning. In SQLite and PostgreSQL the separator always comes before ORDER BY; in MySQL it comes last, after the keyword SEPARATOR.

Lists for people, JSON for programs

A comma-separated list is made for reading. When a program will take the list apart again, a separator is fragile:

A text list and a JSON array of the same values SQL · json_array.sql

-- One product name contains a comma, so a comma-separated list cannot be split back.
CREATE TABLE order_items (order_id INTEGER, product TEXT);
INSERT INTO order_items VALUES
  (301, 'Rice, basmati, 5 kg'), (301, 'Salt 1 kg'), (301, NULL);

SELECT group_concat(product, ', ' ORDER BY product)    AS as_text,
       json_group_array(product ORDER BY product)      AS as_json
FROM order_items
GROUP BY order_id;

Output

┌────────────────────────────────┬──────────────────────────────────────────┐
│            as_text             │                 as_json                  │
├────────────────────────────────┼──────────────────────────────────────────┤
│ Rice, basmati, 5 kg, Salt 1 kg │ [null,"Rice, basmati, 5 kg","Salt 1 kg"] │
└────────────────────────────────┴──────────────────────────────────────────┘

Recorded with sql.js 1.14.2 (SQLite 3.49.1) on macOS 26 arm64. To run it yourself: sqlite3 -box :memory: < json_array.sql

One product name contains commas, so nobody can tell from as_text how many products there were. The JSON array is unambiguous: each value is quoted, a comma inside a name stays inside its quotes, and the program gets a real list. Notice that json_group_array keeps the NULL as null where group_concat dropped it. PostgreSQL has json_agg and MySQL JSON_ARRAYAGG for the same purpose; the JSON module covers them.

How the engines differ

String aggregation in PostgreSQL, MySQL and SQLite
Criterion	PostgreSQL 18	MySQL 8.4	SQLite 3.49
Name	string_agg	GROUP_CONCAT	group_concat or string_agg
Separator	Second argument, required	After SEPARATOR; a comma if none	Second argument; a comma if none
Order of the values	ORDER BY after the separator	ORDER BY before SEPARATOR	ORDER BY after the separator
DISTINCT and a separator	Yes	Yes	No: use a subquery
Misplaced ORDER BY, as in the mistake above	An error	Runs, with commas	Runs, with commas
Length limit	None of its own	1,024 bytes by default, then cut	None of its own

Written out, the same ordered, comma-and-space list is string_agg(name, ', ' ORDER BY name) in PostgreSQL, GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') in MySQL and group_concat(name, ', ' ORDER BY name) in SQLite. With DISTINCT it becomes string_agg(DISTINCT name, ', ' ORDER BY name) and GROUP_CONCAT(DISTINCT name ORDER BY name SEPARATOR ', ').

The MySQL limit deserves a warning of its own. When a list grows past group_concat_max_len, MySQL does not fail: it cuts the text and only raises warning 1260, which an application sees only if it checks for warnings, for example with SHOW WARNINGS. If the lists can be long, raise the limit for the session (SET SESSION group_concat_max_len = …) or return the values as rows instead.

SQLite Online (SQL Playground) Open a SQLite database file of your own in the browser and try group_concat on its tables.

Key takeaways

group_concat(value, separator) in SQLite, GROUP_CONCAT(value SEPARATOR separator) in MySQL and string_agg(value, separator) in PostgreSQL and SQLite 3.44+ join a group’s values into one string.
The order of the values is undefined unless you write ORDER BY inside the call, after the last argument.
NULLs are skipped, and a group of only NULLs gives NULL; use coalesce for a placeholder.
In SQLite, DISTINCT works only without your own separator: remove duplicates in a subquery first.
MySQL cuts long results at group_concat_max_len (1,024 bytes by default), with only a warning.
For lists that a program will read, a JSON array is safer than a separated string.

Exercise

Exercise · Easy · SQL

The products of each order, alphabetically

The table order_items has one row per product on an order:

order_id: the order;
product: the product's name, or NULL when it is not known yet.

Write a query that returns one row per order with two columns:

order_id;
products: the names of the order's products in alphabetical order, separated by a comma and a space (, ).

Lines whose product is NULL are left out of the list. The rows may come in any order, but the names inside each list must be in alphabetical order.

Starter code · product_list.sql

-- One row per order: its product names, alphabetically, separated by ', '.
SELECT order_id,
       group_concat(product) AS products
FROM order_items
GROUP BY order_id;

The sample tests · tests.yaml

# Sample tests: each one builds a small order_items table, runs your query on it and compares the rows it returns (in
# any order; the names inside each list must be in alphabetical order).
tests:
  - name: lists each order's products alphabetically
    setup: |
      CREATE TABLE order_items (order_id INTEGER, product TEXT);
      INSERT INTO order_items VALUES
        (101, 'Toor dal 1 kg'),
        (102, 'Milk 1 l'),
        (101, 'Basmati rice 5 kg'),
        (103, 'Paneer 200 g'),
        (102, 'Bread'),
        (101, 'Atta 5 kg');
    expected:
      columns: [order_id, products]
      rows:
        - [101, 'Atta 5 kg, Basmati rice 5 kg, Toor dal 1 kg']
        - [102, 'Bread, Milk 1 l']
        - [103, 'Paneer 200 g']
  - name: sorts names that share their first words
    setup: |
      CREATE TABLE order_items (order_id INTEGER, product TEXT);
      INSERT INTO order_items VALUES
        (201, 'Atta 5 kg'),
        (201, 'Aloo bhujia 200 g'),
        (201, 'Atta 1 kg'),
        (202, 'Sugar 1 kg'),
        (202, 'Salt 1 kg');
    expected:
      columns: [order_id, products]
      rows:
        - [201, 'Aloo bhujia 200 g, Atta 1 kg, Atta 5 kg']
        - [202, 'Salt 1 kg, Sugar 1 kg']
  - name: leaves NULL products out of the list
    setup: |
      CREATE TABLE order_items (order_id INTEGER, product TEXT);
      INSERT INTO order_items VALUES
        (301, 'Jaggery 1 kg'),
        (301, NULL),
        (301, 'Ghee 500 ml'),
        (302, 'Curd 500 g');
    expected:
      columns: [order_id, products]
      rows:
        - [301, 'Ghee 500 ml, Jaggery 1 kg']
        - [302, 'Curd 500 g']

A hint

Group by order_id and join the names with group_concat(product, ', ' ORDER BY product). The separator comes before ORDER BY inside the call; if you write it after, the list loses its spaces.

The sample tests run on this device, in your browser (sql.js): nothing is sent to mysmartcopilot.com. The first run downloads SQLite (about 0.7 MB), which is kept for the next runs. A check in your browser is feedback for you, not proof that the code is right for every input.

Check yourself

5 questions about this lesson. Every answer and why it is right is on the page, behind “Show the answer”. Your score stays in this browser.

Question 1 of 5 The ORDER BY sits before the separator. What does SQLite print?
What does this program print? Choose one answer.
```
CREATE TABLE items (order_id INTEGER, product TEXT);
INSERT INTO items VALUES (1, 'tea'), (1, 'rice'), (1, 'dal');

-- The ORDER BY is inside the call, but before the separator.
SELECT group_concat(product ORDER BY product, ', ') AS products
FROM items;
```
┌──────────────┐ │ products │ ├──────────────┤ │ tea,rice,dal │ └──────────────┘ ┌──────────────┐ │ products │ ├──────────────┤ │ dal,rice,tea │ └──────────────┘ ┌────────────────┐ │ products │ ├────────────────┤ │ dal, rice, tea │ └────────────────┘ ┌────────────────┐ │ products │ ├────────────────┤ │ tea, rice, dal │ └────────────────┘
Show the answer to question 1
Answer: it prints
```
┌──────────────┐
│   products   │
├──────────────┤
│ dal,rice,tea │
└──────────────┘
```
SQLite reads ORDER BY product, ', ' as an ordering by two keys and calls group_concat with one argument, so the values are sorted (dal, rice, tea) but joined with the default bare comma. Write group_concat(product, ', ' ORDER BY product) to keep the space.
Question 2 of 5 A query uses group_concat(product, ', ') with no ORDER BY inside the call. In what order are the products listed?
Choose one answer.

Alphabetically In no defined order; it can change with the data, an index or a new engine version In the order of the query's final ORDER BY clause In the order the rows were inserted, always

Show the answer to question 2

Answer: In no defined order; it can change with the data, an index or a new engine version

Without ORDER BY inside the call, the values are joined in whatever order the engine reads them, which no engine promises to keep. The query's last ORDER BY sorts the result rows, not the values inside each list.
Question 3 of 5 Every product of order 203 is NULL. What does group_concat(product, ', ') return for that order?
Choose one answer.

NULL An empty string A single comma and a space The text NULL

Show the answer to question 3

Answer: NULL

String aggregation skips NULLs, like the other aggregates. With no value left to join, the result is NULL, not an empty string. Use coalesce(group_concat(product, ', '), '-') to show a placeholder.
Question 4 of 5 In MySQL 8.4 with default settings, what happens when the list built by GROUP_CONCAT would be 5,000 bytes long?
Choose one answer.

MySQL returns NULL MySQL returns the whole 5,000-byte string The query fails with an error It is cut at 1,024 bytes and MySQL adds a warning, not an error

Show the answer to question 4

Answer: It is cut at 1,024 bytes and MySQL adds a warning, not an error

group_concat_max_len is 1,024 bytes by default, and a longer result is truncated with only a warning (1260), which an application sees only if it checks for warnings. Raise the limit for the session when the lists can be long.
Question 5 of 5 What happens when SQLite 3.49 runs this query?
Read the code, then choose one answer.
```
SELECT rider, group_concat(DISTINCT city, '; ')
FROM deliveries
GROUP BY rider;
```
Each rider's different cities, separated by a semicolon and a space Each rider's different cities, separated by a comma An error, because DISTINCT works only for an aggregate with one argument Each rider's cities with duplicates, because DISTINCT is ignored
Show the answer to question 5

Answer: An error, because DISTINCT works only for an aggregate with one argument

SQLite allows DISTINCT only when the aggregate takes a single argument, and this call has two, so it reports that DISTINCT aggregates must have exactly one argument. Remove the duplicates in a subquery (SELECT DISTINCT rider, city FROM deliveries) and join what is left.

References

PostgreSQL documentation: Aggregate Functions (string_agg) (The PostgreSQL Global Development Group)
PostgreSQL documentation: Value Expressions (ORDER BY in aggregate expressions) (The PostgreSQL Global Development Group)
SQLite: Built-in Aggregate Functions (SQLite)
SQLite: Release History (version 3.44.0) (SQLite)
SQLite: JSON Functions And Operators (json_group_array) (SQLite)
MySQL 8.4 Reference Manual: Aggregate Function Descriptions (GROUP_CONCAT) (Oracle Corporation)
MySQL 8.4 Reference Manual: Server System Variables (group_concat_max_len) (Oracle Corporation)
MySQL 8.4 Error Reference: Server Error Message Reference (error 1260) (Oracle Corporation)
MySQL 8.4 Reference Manual: SHOW WARNINGS Statement (Oracle Corporation)

Report a problem with this lesson

Your country

String aggregation: GROUP_CONCAT and STRING_AGG

What you will learn

Before you start

One string per group

Choosing the order

Duplicates and DISTINCT

NULLs and empty groups

A common mistake: ORDER BY in the wrong place

Lists for people, JSON for programs

How the engines differ

Key takeaways

Exercise

The products of each order, alphabetically

Results of the sample tests

Check yourself

References

Related tools