Module 3's aggregate functions collapse a group of numbers into one number, COUNT, SUM, AVG. String aggregation is the text equivalent: collapsing a group's text values into one delimited string. Beacon Robotics wants one row per project listing every assigned employee's name, comma-separated, instead of one row per assignment.
Learning Objectives
- Define string aggregation, and write it under each database's own real function name.
- Order the values inside the aggregated string itself, not just the rows of the outer query.
- Know genuinely which database uses which of the three names: GROUP_CONCAT, STRING_AGG, LISTAGG.
Group Rows
One group per project
Aggregate Text
Collapse multiple values into one string
One Delimited String
Per group
The Three Names, by Database
| Database | String Aggregation |
|---|---|
| MySQL / MariaDB | GROUP_CONCAT(col SEPARATOR ', ') |
| SQLite | GROUP_CONCAT(col, ', ') — a comma-separated second argument, not MySQL's SEPARATOR keyword, defaults to a comma if left out |
| PostgreSQL | STRING_AGG(col, ', ') — the separator argument is mandatory, there's no default |
| SQL Server | STRING_AGG(col, ', ') — added in SQL Server 2017, earlier versions have no equivalent function at all |
| Oracle | LISTAGG(col, ', ') WITHIN GROUP (ORDER BY col) — the WITHIN GROUP ORDER BY clause is mandatory syntax here, not optional |
Ordering the Values Inside the String
| Database | Adding an ORDER BY Inside the Aggregate |
|---|---|
| MySQL / MariaDB | GROUP_CONCAT(col ORDER BY col SEPARATOR ', ') — ORDER BY goes inside the parentheses, before SEPARATOR |
| PostgreSQL | STRING_AGG(col, ', ' ORDER BY col) — ORDER BY written directly inside the call, after the separator |
| SQL Server / Oracle | STRING_AGG(col, ', ') WITHIN GROUP (ORDER BY col) / LISTAGG(col, ', ') WITHIN GROUP (ORDER BY col) — a separate WITHIN GROUP clause, the same pattern on both |
SQLite's GROUP_CONCAT has no ORDER BY option at all in most still-common versions (it only gained one, inside the parentheses, in SQLite 3.44.0, a recent release), the output order is otherwise arbitrary. The portable fix on an older SQLite is to sort first in a subquery, then aggregate over that already-sorted result.
Try It Yourself
List every employee's last name assigned to project id 4, comma-separated and alphabetically ordered, on PostgreSQL.
SELECT STRING_AGG(e.last_name, ', ' ORDER BY e.last_name) AS team FROM project_assignments pa JOIN employees e ON e.id = pa.employee_id WHERE pa.project_id = 4;
Frequently Asked Questions
Is there a maximum length for the aggregated string?
Yes on most of these, MySQL's GROUP_CONCAT in particular is capped by the group_concat_max_len session variable, silently truncating past that length rather than erroring, worth raising it explicitly before aggregating a genuinely large group.
Can duplicate values be removed before aggregating?
On MySQL/MariaDB, GROUP_CONCAT(DISTINCT col ...) works directly. PostgreSQL's STRING_AGG also accepts DISTINCT directly. SQL Server's STRING_AGG does not support DISTINCT at all, deduplicate in a subquery first there instead.
Common Beginner Mistakes
Writing LISTAGG(col, ', ') on Oracle without a WITHIN GROUP (ORDER BY ...) clause
Oracle's LISTAGG requires that clause, it's not optional the way an ORDER BY is for most other aggregates. Leaving it out is a syntax error on Oracle, not just a missing nice-to-have.
Expecting GROUP_CONCAT's syntax to work unchanged on PostgreSQL or SQL Server
It won't, those two use STRING_AGG with a mandatory separator argument, not GROUP_CONCAT with an optional SEPARATOR keyword. The concept is identical, the function name and argument style genuinely aren't.
Best Practices
- Always specify an explicit ORDER BY inside the aggregate when the output order matters, don't rely on whatever order rows happen to arrive in.
- Check and raise MySQL's group_concat_max_len before aggregating a group that could be long, the default silently truncates rather than erroring.
- Reach for LISTAGG/STRING_AGG/GROUP_CONCAT only for display-style output, not as a stand-in for a properly normalized multi-row result a client application should just receive as rows.
Interview Questions
Name the string-aggregation function on three different databases, and the one syntax detail that's mandatory on one of them but optional on the others.
MySQL/MariaDB use GROUP_CONCAT, PostgreSQL and SQL Server use STRING_AGG, Oracle uses LISTAGG. Oracle's LISTAGG uniquely requires a WITHIN GROUP (ORDER BY ...) clause, every other listed function treats ordering as optional.
Why might GROUP_CONCAT on MySQL silently return a truncated string instead of an error?
GROUP_CONCAT's output length is capped by the group_concat_max_len session variable, by default low enough to truncate a genuinely large group. It cuts the string off at that length rather than raising an error, easy to miss unless the variable is explicitly checked and raised when a long result is actually expected.
Summary
String aggregation collapses a group's text values into one delimited string, under three genuinely different names, GROUP_CONCAT (MySQL/MariaDB/SQLite), STRING_AGG (PostgreSQL/SQL Server 2017+), LISTAGG (Oracle, with a mandatory WITHIN GROUP ORDER BY). Ordering the values inside the string is its own per-database syntax detail, worth getting right explicitly rather than assuming an arrival order.
What's Next?
The next lesson covers ARRAY_AGG, PostgreSQL's native way to collapse a group into an actual array value, not just a string, and what the other five databases do instead since none of them have PostgreSQL's array type.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.
Or try 50 SQL practice questions →