String aggregation with STRING_AGG in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the STRING_AGG function do in PostgreSQL?

STRING_AGG combines multiple string values from rows into a single string, separated by a specified delimiter.

beginner

How do you specify the separator in STRING_AGG?

You provide the separator as the second argument, for example: STRING_AGG(column_name, ', ') combines values separated by a comma and space.

intermediate

Can STRING_AGG be used with an ORDER BY clause? If yes, why?

Yes, STRING_AGG supports ORDER BY inside its syntax to control the order of concatenated strings, ensuring predictable output.

beginner

Write a simple example of STRING_AGG usage to combine names separated by a semicolon.

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

intermediate

What happens if STRING_AGG is used on an empty set of rows?

STRING_AGG returns NULL if there are no rows to aggregate.

What is the purpose of STRING_AGG in PostgreSQL?

A To combine multiple string values into one string with a separator

B To count the number of rows in a table

C To find the maximum value in a column

D To split a string into multiple rows

STRING_AGG combines multiple strings into one, separated by a specified delimiter.

How do you specify the separator in STRING_AGG?

A As the second argument

B As the first argument

C Using a separate function

D It uses a default separator and cannot be changed

The separator is the second argument in STRING_AGG, for example: STRING_AGG(column, ', ').

Which clause can be used inside STRING_AGG to order the concatenated strings?

A GROUP BY

B HAVING

C ORDER BY

D WHERE

ORDER BY inside STRING_AGG controls the order of concatenated strings.

What does STRING_AGG return if no rows are found?

A An error

B An empty string

C Zero

D NULL

STRING_AGG returns NULL when there are no rows to aggregate.

Which of the following is a valid STRING_AGG usage?

A STRING_AGG(name)

B STRING_AGG(name, ', ' ORDER BY name)

C STRING_AGG(name, ', ', GROUP BY name)

D STRING_AGG(name, ', ', WHERE age > 20)

STRING_AGG can include ORDER BY inside parentheses to order the concatenated strings.

Explain how STRING_AGG works and how you can control the order of concatenated strings.

Think about how to join many names into one string with a chosen separator and order.

Write a query using STRING_AGG to list all product names separated by a dash (-), ordered alphabetically.

Use STRING_AGG(product_name, ' - ' ORDER BY product_name) from products.

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