Aggregation

Aggregating and shaping query output in PostgreSQL

By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17

PostgreSQL renames most of the functions that collect many rows into one value. Some replacements need an argument your old engine let you leave out. A few run without complaint and give you the wrong number.

Find what you typed in the table below. Each row gives the error PostgreSQL raises, the call that replaces it, and a guide.

What to write instead, by engine

You wrote	PostgreSQL says	Write instead
`GROUP_CONCAT(city)` MySQL GROUP_CONCAT	`function group_concat(text) does not exist`	`string_agg(city, ', ')` The delimiter is a required argument in PostgreSQL. MySQL defaults it to a comma. GROUP_CONCAT guide
`STRING_AGG(city)` SQL Server STRING_AGG	`function string_agg(text) does not exist`	`string_agg(city, ', ')` The function exists, but no overload takes one argument. PostgreSQL names the argument types in this error whether or not the name exists at all, so it reads like an absence. why the one-argument call fails
`LISTAGG(city, ',')` Oracle LISTAGG	`function listagg(text, unknown) does not exist`	`string_agg(city, ',')` Keep the delimiter the reader wrote. A space after the comma is a different string. collecting rows into one string
`SELECT name FROM customers FOR JSON PATH` SQL Server FOR JSON	`syntax error at or near "JSON"`	`jsonb_agg(jsonb_build_object('name', name))` Two functions, not a clause: one shapes each object, the other collects them. SQL Server drops null fields from its output by default and this keeps them, so the documents are not the same shape. returning JSON from a query
`JSON_OBJECTAGG(city, id)` MySQL JSON_OBJECTAGG	`syntax error at or near ","`	`jsonb_object_agg(city, id)` PostgreSQL 16 added the SQL standard JSON_OBJECTAGG too, but it takes its pair as key VALUE value. The comma form is MySQL’s. Either way the key may not be null, and 9 of these cities are. building JSON objects
`PIVOT (count(id) FOR status IN ('pending'))` SQL Server PIVOT	`syntax error at or near "("`	`count(*) FILTER (WHERE status = 'pending')` One aggregate per column, and no extension to install. pivoting rows into columns

One entry is missing from that table on purpose. crosstab, the function most answers reach for when pivoting, lives in the tablefunc extension, which ships with PostgreSQL and is switched on per database. Whether you have it depends on your server, not on PostgreSQL, so the pivot guide covers it separately.

When the function has a different name here

You used another engine's function name and PostgreSQL rejected it.

What replaces GROUP_CONCAT in PostgreSQL? string_agg needs a delimiter, and the one-argument call fails with an error about the function not existing.
How do you sort the values inside STRING_AGG? ORDER BY belongs inside the parentheses. At the end of the query it raises a GROUP BY error instead.
How do you return JSON from a PostgreSQL query? jsonb_build_object for the shape, jsonb_agg to collect the rows, and one nested document from two tables.
How do you pivot rows into columns in PostgreSQL? One aggregate with a FILTER clause per column, with no extension to install.

When the query runs and the answer is wrong

The query runs and PostgreSQL does not complain. The number or the list is quietly not what you meant.

Why does json_agg return null instead of an empty array? Wrap it in coalesce. After a LEFT JOIN you get [null] instead, and coalesce will not fix that one.
Which rows do SQL aggregate functions skip? Most skip rows whose input is null. COUNT(*), array_agg and jsonb_agg do not, so two counts disagree.

The concepts these guides build on

Each of these is a lesson in the free course, with its own practice problems. A guide answers one question; a lesson teaches the mechanism underneath it.

Aggregate Functions (COUNT, SUM, AVG, MIN, MAX) Count, sum and average a column, and see what each one does with the rows it was handed.
GROUP BY Collapse many rows into one per group, and decide what belongs in the group key.
CASE WHEN Expressions Return a different value per row with CASE, which is how a pivot column gets built.
Conditional Aggregation (CASE inside Aggregates) Count or sum only the rows that match a condition, inside a single grouped query.
COALESCE and NULLIF Swap a null for something else with COALESCE, and turn a value back into null with NULLIF.
String Concatenation and Formatting Join text values together inside one row before any aggregate sees them.
Aggregate Window Functions (SUM, AVG, COUNT OVER) Attach OVER to an aggregate and keep every row instead of collapsing them.
DISTINCT ON Keep one row per group with DISTINCT ON, ordered so the row you keep is the one you meant.
STRING_AGG and ARRAY_AGG Collect a column from many rows into one delimited string with string_agg.
JSONB Field Extraction Read values out of a JSONB column with the arrow operators and jsonpath.
JSONB Aggregation (jsonb_agg, json_build_object) Build JSON objects and arrays out of query results with jsonb_build_object and jsonb_agg.

Where to practise this

Every guide in this cluster ends in two graded exercises that run in your browser, and every concept page carries the practice problems for its own lesson. No account, nothing to install, nothing to pay.

If you would rather work through aggregation in order than answer one question, the library index lists all 66 concepts, and the course itself picks the next one for you and schedules review as you go.