aggregate
STRING_AGG / GROUP_CONCAT
Concatenates non-NULL string values across a group into a single delimited string. Postgres and SQL Server spell it STRING_AGG; MySQL and SQLite use GROUP_CONCAT with a SEPARATOR clause.
STRING_AGG(expr, delim [ORDER BY ...]) | GROUP_CONCAT(expr SEPARATOR ',')Parameters / Modifiers
| Parameter | Purpose |
|---|---|
| ORDER BY | Postgres: control the order of concatenated pieces |
| DISTINCT | Concatenate unique values only (supported by MySQL GROUP_CONCAT) |
| SEPARATOR 'x' | MySQL/SQLite syntax for the delimiter |
| group_concat_max_len | MySQL session variable that truncates long results (default 1024) |
Examples
SELECT STRING_AGG(name, ', ' ORDER BY name) FROM users;Postgres: comma-separated alphabetized name list
SELECT GROUP_CONCAT(name SEPARATOR ', ') FROM users;MySQL/SQLite equivalent of the previous query
SELECT dept, STRING_AGG(DISTINCT skill, '|') FROM emp GROUP BY dept;Postgres: pipe-separated unique skills per department
SELECT GROUP_CONCAT(DISTINCT tag ORDER BY tag) FROM posts;MySQL: sorted unique tags in one string
Dialect notes / Gotcha
MySQL silently truncates at group_concat_max_len (default 1024) — bump the session var for large lists. Postgres requires an explicit delimiter argument; passing '' concatenates without one.
Related functions
ARRAY_AGG
Collects grouped values into a single array, preserving order when ORDER BY is supplied. This is a Postgres feature; MySQL has no direct array type but JSON_ARRAYAGG serves the same role.
CONCAT
Joins two or more strings end-to-end and returns a single string. CONCAT is portable across MySQL, Postgres 9.1+, and SQLite 3.44+; the standard || operator works in Postgres and SQLite but not MySQL unless PIPES_AS_CONCAT is set.
jsonb_agg
Postgres aggregate that collects rows into a jsonb array. Its sibling jsonb_object_agg builds a jsonb object from key/value pairs.
COUNT
Returns the number of rows matching a query, optionally counting non-NULL values of an expression or distinct values. COUNT(*) counts every row including NULLs, while COUNT(expr) skips NULLs.
SUM
Adds up all non-NULL numeric values in a column or expression. Returns NULL (not 0) if every input row is NULL or the result set is empty.
← All SQL functions · SQL cheat sheet · Window functions · PostgreSQL cheat sheet