String aggregation with STRING_AGG in PostgreSQL - Step-by-Step Execution

Concept Flow - String aggregation with STRING_AGG

Start Query

↓

Group rows by key

↓

Collect strings per group

↓

Concatenate strings with separator

↓

Return aggregated string per group

↓

End Query

The query groups rows by a key, collects strings from each group, concatenates them with a separator, and returns the result.

Execution Sample

PostgreSQL

SELECT department, STRING_AGG(employee_name, ', ') AS employees
FROM staff
GROUP BY department;

This query groups employees by their department and combines their names into a single comma-separated string per department.

Execution Table

Step	Action	Group Key	Strings Collected	Concatenated Result
1	Start query execution	-	-	-
2	Group rows by department	Sales	Alice	Alice
3	Add next employee to Sales group	Sales	Alice, Bob	Alice, Bob
4	Group rows by department	HR	Carol	Carol
5	Add next employee to HR group	HR	Carol, Dave	Carol, Dave
6	Group rows by department	IT	Eve	Eve
7	Concatenate strings for Sales	Sales	Alice, Bob	Alice, Bob
8	Concatenate strings for HR	HR	Carol, Dave	Carol, Dave
9	Concatenate strings for IT	IT	Eve	Eve
10	Return final result set	-	-	-

💡 All groups processed and strings aggregated, query execution complete.

Variable Tracker

Variable	Start	After Step 3	After Step 5	After Step 9	Final
Sales group strings	empty	Alice, Bob	Alice, Bob	Alice, Bob	Alice, Bob
HR group strings	empty	empty	Carol, Dave	Carol, Dave	Carol, Dave
IT group strings	empty	empty	empty	Eve	Eve

Key Moments - 2 Insights

Why do we need to use GROUP BY with STRING_AGG?

STRING_AGG combines strings per group, so GROUP BY defines which rows belong together. Without GROUP BY, STRING_AGG would combine all rows into one string (see execution_table steps 2 and 4).

What happens if a group has only one string?

STRING_AGG returns that single string without adding the separator (see IT group in execution_table steps 6 and 9).

Visual Quiz - 3 Questions

Look at the execution_table, what is the concatenated result for the HR group after step 5?

A Carol

B Carol, Dave

C Dave

D Carol, Bob

💡 Hint

Check the 'Concatenated Result' column for HR group at step 5.

At which step does the Sales group first have two employees collected?

A Step 2

B Step 7

C Step 3

D Step 9

💡 Hint

Look at the 'Strings Collected' column for Sales group in execution_table.

If we remove GROUP BY, how would the final result change?

A All employee names would be combined into one string

B Each department would have its own aggregated string

C Query would return an error

D Only one employee name would be returned

💡 Hint

Recall that GROUP BY defines grouping; without it, STRING_AGG aggregates all rows together.

Concept Snapshot

STRING_AGG(expression, separator) aggregates strings from grouped rows.
Use with GROUP BY to combine values per group.
Returns concatenated string separated by given separator.
If group has one value, returns it as is.
Useful for listing items in one row per group.

Full Transcript

This visual execution traces how STRING_AGG works in PostgreSQL. The query groups rows by department and collects employee names per group. Then it concatenates these names with a comma and space separator. The execution table shows each step: grouping rows, collecting strings, and concatenating them. Variables track the strings collected per group as they grow. Key moments clarify why GROUP BY is needed and what happens with single-item groups. The quiz tests understanding of the aggregation process and effects of removing GROUP BY. The snapshot summarizes syntax and behavior for quick reference.

Practice

1. What does the STRING_AGG function do in PostgreSQL?

easy

A. It combines multiple text values into one string with a separator.

B. It counts the number of rows in a table.

C. It sorts rows based on a column.

D. It deletes duplicate rows from a table.

Solution

1. Step 1: Understand STRING_AGG purpose

   STRING_AGG is used to join multiple text values into a single string with a separator.

2. Step 2: Compare with other options

   Counting rows, sorting, or deleting duplicates are different functions, not STRING_AGG.

3. Final Answer:

   It combines multiple text values into one string with a separator. -> Option A

4. Quick Check:

   STRING_AGG joins text values = It combines multiple text values into one string with a separator. [OK]

Hint: Remember: STRING_AGG joins strings with a separator [OK]

Common Mistakes:

• Confusing STRING_AGG with COUNT or ORDER BY
• Thinking STRING_AGG sorts rows
• Assuming STRING_AGG removes duplicates

2. Which of the following is the correct syntax to aggregate names separated by commas using STRING_AGG in PostgreSQL?

easy

A. SELECT STRING_AGG(name, ',') FROM users ORDER BY name;

B. SELECT STRING_AGG(name) FROM users;

C. SELECT STRING_AGG(name, ',') ORDER BY name FROM users;

D. SELECT STRING_AGG(name, ',') FROM users;

Solution

1. Step 1: Check basic STRING_AGG syntax

   STRING_AGG requires two arguments: the column and the separator, e.g., STRING_AGG(name, ',').

2. Step 2: Validate query structure

   ORDER BY inside STRING_AGG needs to be inside parentheses, not outside. SELECT STRING_AGG(name, ',') FROM users; is correct syntax without ordering.

3. Final Answer:

   SELECT STRING_AGG(name, ',') FROM users; -> Option D

4. Quick Check:

   Correct STRING_AGG syntax = SELECT STRING_AGG(name, ',') FROM users; [OK]

Hint: STRING_AGG needs column and separator inside parentheses [OK]

Common Mistakes:

• Omitting separator argument
• Placing ORDER BY outside STRING_AGG
• Using STRING_AGG without parentheses

3. Given the table fruits with rows: ('apple'), ('banana'), ('cherry'), what is the result of this query?

SELECT STRING_AGG(name, '; ' ORDER BY name) FROM fruits;

medium

A. 'apple; banana; cherry'

B. 'banana; apple; cherry'

C. 'cherry; banana; apple'

D. 'apple, banana, cherry'

Solution

1. Step 1: Understand ORDER BY inside STRING_AGG

   The query orders names alphabetically: apple, banana, cherry.

2. Step 2: Check separator and output

   Separator is '; ', so the result joins names with '; ' in order.

3. Final Answer:

   'apple; banana; cherry' -> Option A

4. Quick Check:

   Ordered join with '; ' separator = 'apple; banana; cherry' [OK]

Hint: ORDER BY inside STRING_AGG sorts before joining [OK]

Common Mistakes:

• Ignoring ORDER BY inside STRING_AGG
• Using wrong separator
• Assuming default comma separator

4. Identify the error in this query:

SELECT STRING_AGG(name, ', ' ORDER BY name) FROM users ORDER BY name;

medium

A. ORDER BY cannot be used inside STRING_AGG.

B. The query is correct and will run without errors.

C. ORDER BY must be inside STRING_AGG parentheses, not outside.

D. ORDER BY inside STRING_AGG must come after separator argument.

Solution

1. Step 1: Check ORDER BY usage inside STRING_AGG

   ORDER BY inside STRING_AGG after the separator is valid syntax.

2. Step 2: Check outer ORDER BY

   Without GROUP BY, the outer ORDER BY name causes an error because 'name' must appear in GROUP BY or be used in an aggregate function.

3. Final Answer:

   ORDER BY must be inside STRING_AGG parentheses, not outside. -> Option C

4. Quick Check:

   Outer ORDER BY invalid without GROUP BY = ORDER BY must be inside STRING_AGG parentheses, not outside. [OK]

Hint: ORDER BY inside STRING_AGG works; outside requires GROUP BY in aggregate queries [OK]

Common Mistakes:

• Thinking ORDER BY inside STRING_AGG is invalid
• Confusing outer ORDER BY with inner
• Misplacing ORDER BY arguments

5. You have a table orders with columns customer_id and product. How do you write a query to list each customer with a semicolon-separated list of their products sorted alphabetically?

hard

A. SELECT customer_id, STRING_AGG(product, '; ') ORDER BY product FROM orders GROUP BY customer_id;

B. SELECT customer_id, STRING_AGG(product, '; ' ORDER BY product) FROM orders GROUP BY customer_id;

C. SELECT customer_id, STRING_AGG(product ORDER BY product, '; ') FROM orders GROUP BY customer_id;

D. SELECT customer_id, STRING_AGG(product, '; ') FROM orders ORDER BY customer_id;

Solution

1. Step 1: Group by customer_id

   We need to group rows by customer_id to aggregate products per customer.

2. Step 2: Use STRING_AGG with ORDER BY inside

   STRING_AGG(product, '; ' ORDER BY product) joins products with '; ' separator sorted alphabetically.

3. Step 3: Validate options

   SELECT customer_id, STRING_AGG(product, '; ' ORDER BY product) FROM orders GROUP BY customer_id; correctly uses GROUP BY and STRING_AGG with ORDER BY inside parentheses.

4. Final Answer:

   SELECT customer_id, STRING_AGG(product, '; ' ORDER BY product) FROM orders GROUP BY customer_id; -> Option B

5. Quick Check:

   Group by customer and ordered STRING_AGG = SELECT customer_id, STRING_AGG(product, '; ' ORDER BY product) FROM orders GROUP BY customer_id; [OK]

Hint: Put ORDER BY inside STRING_AGG parentheses for sorting [OK]

Common Mistakes:

• Placing ORDER BY outside STRING_AGG
• Omitting GROUP BY when aggregating
• Swapping arguments inside STRING_AGG