How to aggregate strings in SELECT list when using GROUP BY?

App Building
1

I have a Retool database. When using GROUP BY COLUMN1, COLUMN2, I want to aggregate COLUMN3, which contains strings. As far as I can tell, this requires that I aggregate COLUMN3.

It appears that some SQLs have a STRING_AGG function. However, Retool does not recognize this. How do I aggregate the COLUMN3 strings when using GROUP BY?

2

I don't quite understand your use case, but have you tried the || operator?

3

I should have used “aggregate” rather than “concatenate” in the title.
The || operator concatenates strings, but it does not aggregate strings, which I need for the GROUP BY.

4

Can you post the query you are trying to correct?

5

The voters table has columns for first name, last name, street number, and street name, &c. I need to create a mailing list that, for each address, has a field that combines all the names into one string, which is what STRING_AGG apparently would do.

SELECT
-- Aggregate Names here,
streetnumber,
streetname
FROM voters
GROUP BY streetnumber,streetname
;

6

You could try :slight_smile:

SELECT
  streetnumber,
  streetname,
  STRING_AGG(name1, ',') AS aggregated_name1,
  STRING_AGG(name2, ',') AS aggregated_name2
FROM voters
GROUP BY streetnumber, streetname;

But are you trying to concatenate all names as one field?

1 Like
7

Thanks. Your example showed me that STRING_AGG(text), which I had tried, does not exist, but STRING_AGG(text, separator) does exist.

1 Like