Thread: Concatenating string fields with GROUP BY clause

Concatenating string fields with GROUP BY clause

From

"Brian G. Huber"

Date:

09 September 2003, 17:04:04

Hi -

I am trying to concatenate a text field in a query with a group by clause, similar to a sum() function on a numeric value - for example:

SELECT groupid, sum(numeric_field), ???(text_field) FROM table GROUP BY groupid

but I cannot find a function that will concatenate the text fields. Any comments appreciated!

TIA,BGH

Re: Concatenating string fields with GROUP BY clause

From

Godshall Michael

Date:

09 September 2003, 17:12:01

Concatenate is done with || in postgresql

-----Original Message-----
From: Brian G. Huber [mailto:brianghuber@yahoo.com]
Sent: Tuesday, September 09, 2003 12:02 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Concatenating string fields with GROUP BY clause

Hi -

I am trying to concatenate a text field in a query with a group by clause, similar to a sum() function on a numeric value - for example:

SELECT groupid, sum(numeric_field), ???(text_field) FROM table GROUP BY groupid

but I cannot find a function that will concatenate the text fields. Any comments appreciated!

TIA,BGH

Re: Concatenating string fields with GROUP BY clause

From

Jason Hihn

Date:

09 September 2003, 17:27:55

Section 6.4

string || string

String concatenation 'Post' || 'greSQL' = PostgreSQL

-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Brian G. Huber
Sent: Tuesday, September 09, 2003 1:02 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Concatenating string fields with GROUP BY clause

Hi -

I am trying to concatenate a text field in a query with a group by clause, similar to a sum() function on a numeric value - for example:

SELECT groupid, sum(numeric_field), ???(text_field) FROM table GROUP BY groupid

but I cannot find a function that will concatenate the text fields. Any comments appreciated!

TIA,BGH

Re: Concatenating string fields with GROUP BY clause

From

"Brian G. Huber"

Date:

09 September 2003, 17:32:14

Thanks for the response - but II does not seem to work as an aggregate function - for example - if I use:

SELECT groupid, sum(numeric_field), ||(text_field) FROM table GROUP BY groupid

I get

ERROR: Unable to identify a prefix operator '||' for type 'text'
You may need to add parentheses or an explicit cast

Therefore, I think II concatenates two text columns, but does not aggregate. It would seem this is fairly common but there is no text aggregation function listed in 6.14 Aggregate Functions!!!

----- Original Message -----

From: Godshall Michael
To: 'Brian G. Huber' ; pgsql-novice@postgresql.org
Sent: Tuesday, September 09, 2003 12:10 PM
Subject: Re: [NOVICE] Concatenating string fields with GROUP BY clause

Concatenate is done with || in postgresql
-----Original Message-----
From: Brian G. Huber [mailto:brianghuber@yahoo.com]
Sent: Tuesday, September 09, 2003 12:02 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Concatenating string fields with GROUP BY clause

Hi -

I am trying to concatenate a text field in a query with a group by clause, similar to a sum() function on a numeric value - for example:

SELECT groupid, sum(numeric_field), ???(text_field) FROM table GROUP BY groupid

but I cannot find a function that will concatenate the text fields. Any comments appreciated!

TIA,BGH

Re: Concatenating string fields with GROUP BY clause

From

Jeffrey Melloy

Date:

09 September 2003, 17:52:56

I think what he's trying to do is this:

 a   |   b      |  g

1    |   sun |  1

2    |   shoe |  1

3    |   pea  |  2

4    |   door |  2


he gets this as the response:

group, sum, concat

 1, 3, sunshoe

  2, 7, peadoor


So a "concatenation aggregate"


That being said, I can't think of any simple way to do it in SQL.


Jeff

On Tuesday, September 9, 2003, at 12:29  PM, Brian G. Huber wrote:


<excerpt><fontfamily><param>Arial</param><color><param>0000,0000,FFFF</param><smaller>Thanks
for the response - but II does not seem to work as an aggregate
function - for example - if I use:</smaller></color></fontfamily>

 

<fontfamily><param>Arial</param><color><param>0000,0000,FFFF</param><smaller>SELECT
groupid, sum(numeric_field), ||(text_field) FROM table GROUP BY groupid</smaller></color></fontfamily>

 

<fontfamily><param>Arial</param><color><param>0000,0000,FFFF</param><smaller>I
get</smaller></color></fontfamily>

 

<fontfamily><param>Arial</param><color><param>0000,0000,FFFF</param><smaller>ERROR: 
Unable to identify a prefix operator '||' for type 'text'

 You may need to add parentheses or an explicit cast</smaller></color></fontfamily>

 

<fontfamily><param>Arial</param><color><param>0000,0000,FFFF</param><smaller>Therefore,
I think II concatenates two text columns, but does not aggregate.  It
would seem this is fairly common but there is no text aggregation
function listed in 6.14 Aggregate Functions!!!</smaller></color></fontfamily>

----- Original Message -----


<bold>From:</bold>
<underline><color><param>1999,1999,FFFF</param>Godshall Michael</color></underline>

<bold>To:</bold> <underline><color><param>1999,1999,FFFF</param>'Brian
G. Huber'</color></underline> ;
<underline><color><param>1999,1999,FFFF</param>pgsql-novice@postgresql.org</color></underline>

<bold>Sent:</bold> Tuesday, September 09, 2003 12:10 PM

<bold>Subject:</bold> Re: [NOVICE] Concatenating string fields with
GROUP BY clause


<fontfamily><param>Arial</param><color><param>0000,0000,FFFF</param><smaller>Concatenate
is done with  ||   in postgresql</smaller></color></fontfamily>


<fontfamily><param>Times New Roman</param><smaller>-----Original
Message-----

<bold>From:</bold> Brian G. Huber [mailto:brianghuber@yahoo.com]

<bold>Sent:</bold> Tuesday, September 09, 2003 12:02 PM

<bold>To:</bold>
<underline><color><param>1999,1999,FFFF</param>pgsql-novice@postgresql.org

</color></underline><bold>Subject:</bold> [NOVICE] Concatenating
string fields with GROUP BY clause


</smaller></fontfamily><fontfamily><param>Arial</param><color><param>0000,0000,FFFF</param><smaller>Hi
-</smaller></color></fontfamily>

 

<fontfamily><param>Arial</param><color><param>0000,0000,FFFF</param><smaller>I
am trying to concatenate a text field in a query with a group by
clause, similar to a sum() function on a numeric value - for example:</smaller></color></fontfamily>

 

<fontfamily><param>Arial</param><color><param>0000,0000,FFFF</param><smaller>SELECT
groupid, sum(numeric_field), ???(text_field) FROM table GROUP BY
groupid</smaller></color></fontfamily>

 

<fontfamily><param>Arial</param><color><param>0000,0000,FFFF</param><smaller>but
I cannot find a function that will concatenate the text fields. Any
comments appreciated!</smaller></color></fontfamily>

 

<fontfamily><param>Arial</param><color><param>0000,0000,FFFF</param><smaller>TIA,BGH</smaller></color></fontfamily>


</excerpt>I think what he's trying to do is this:
  a   |   b      |  g
1    |   sun |  1
2    |   shoe |  1
3    |   pea  |  2
4    |   door |  2

he gets this as the response:
group, sum, concat
  1, 3, sunshoe
   2, 7, peadoor

So a "concatenation aggregate"

That being said, I can't think of any simple way to do it in SQL.

Jeff
On Tuesday, September 9, 2003, at 12:29  PM, Brian G. Huber wrote:

> Thanks for the response - but II does not seem to work as an aggregate
> function - for example - if I use:
>  
> SELECT groupid, sum(numeric_field), ||(text_field) FROM table GROUP BY
> groupid
>  
> I get
>  
> ERROR:  Unable to identify a prefix operator '||' for type 'text'
>  You may need to add parentheses or an explicit cast
>  
> Therefore, I think II concatenates two text columns, but does not
> aggregate.  It would seem this is fairly common but there is no text
> aggregation function listed in 6.14 Aggregate Functions!!!
> ----- Original Message -----
>
> From: Godshall Michael
> To: 'Brian G. Huber' ; pgsql-novice@postgresql.org
> Sent: Tuesday, September 09, 2003 12:10 PM
> Subject: Re: [NOVICE] Concatenating string fields with GROUP BY clause
>
> Concatenate is done with  ||   in postgresql
>
> -----Original Message-----
> From: Brian G. Huber [mailto:brianghuber@yahoo.com]
> Sent: Tuesday, September 09, 2003 12:02 PM
> To: pgsql-novice@postgresql.org
> Subject: [NOVICE] Concatenating string fields with GROUP BY clause
>
> Hi -
>  
> I am trying to concatenate a text field in a query with a group by
> clause, similar to a sum() function on a numeric value - for example:
>  
> SELECT groupid, sum(numeric_field), ???(text_field) FROM table GROUP
> BY groupid
>  
> but I cannot find a function that will concatenate the text fields.
> Any comments appreciated!
>  
> TIA,BGH
>

Re: Concatenating string fields with GROUP BY clause

From

"Brian G. Huber"

Date:

09 September 2003, 18:05:05

Thanks for the example Jeff - Well said - that is what I'm trying to do.

----- Original Message -----
From: Jeffrey Melloy
To: Brian G. Huber
Cc: Godshall Michael ; pgsql-novice@postgresql.org
Sent: Tuesday, September 09, 2003 12:52 PM
Subject: Re: [NOVICE] Concatenating string fields with GROUP BY clause

I think what he's trying to do is this:
a | b | g
1 | sun | 1
2 | shoe | 1
3 | pea | 2
4 | door | 2

he gets this as the response:
group, sum, concat
1, 3, sunshoe
2, 7, peadoor

So a "concatenation aggregate"

That being said, I can't think of any simple way to do it in SQL.

Jeff
On Tuesday, September 9, 2003, at 12:29 PM, Brian G. Huber wrote:

Thanks for the response - but II does not seem to work as an aggregate function - for example - if I use:

SELECT groupid, sum(numeric_field), ||(text_field) FROM table GROUP BY groupid

I get

ERROR: Unable to identify a prefix operator '||' for type 'text'
You may need to add parentheses or an explicit cast

Therefore, I think II concatenates two text columns, but does not aggregate. It would seem this is fairly common but there is no text aggregation function listed in 6.14 Aggregate Functions!!!
----- Original Message -----

From: Godshall Michael
To: 'Brian G. Huber' ; pgsql-novice@postgresql.org
Sent: Tuesday, September 09, 2003 12:10 PM
Subject: Re: [NOVICE] Concatenating string fields with GROUP BY clause

Concatenate is done with || in postgresql

-----Original Message-----
From: Brian G. Huber [mailto:brianghuber@yahoo.com]
Sent: Tuesday, September 09, 2003 12:02 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Concatenating string fields with GROUP BY clause

Hi -

I am trying to concatenate a text field in a query with a group by clause, similar to a sum() function on a numeric value - for example:

SELECT groupid, sum(numeric_field), ???(text_field) FROM table GROUP BY groupid

but I cannot find a function that will concatenate the text fields. Any comments appreciated!

TIA,BGH

Re: Concatenating string fields with GROUP BY clause

From

Jason Hihn

Date:

09 September 2003, 19:58:17

I was just working on a similar problem. I resulted in to doing it in code. HAVING would be the avenue, but it is just like a where clause and you can't say HAVING previous record's g field=this record's g field. :-( You must sort first by the g field then just spin through while the g fields are the same.

If anyone knows how to to it within SQL only, I'd appreciate knowing too.

-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Brian G. Huber
Sent: Tuesday, September 09, 2003 2:03 PM
To: Jeffrey Melloy
Cc: Godshall Michael; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Concatenating string fields with GROUP BY clause

Thanks for the example Jeff - Well said - that is what I'm trying to do.
----- Original Message -----
From: Jeffrey Melloy
To: Brian G. Huber
Cc: Godshall Michael ; pgsql-novice@postgresql.org
Sent: Tuesday, September 09, 2003 12:52 PM
Subject: Re: [NOVICE] Concatenating string fields with GROUP BY clause

I think what he's trying to do is this:
a | b | g
1 | sun | 1
2 | shoe | 1
3 | pea | 2
4 | door | 2

he gets this as the response:
group, sum, concat
1, 3, sunshoe
2, 7, peadoor

So a "concatenation aggregate"

That being said, I can't think of any simple way to do it in SQL.

Jeff
On Tuesday, September 9, 2003, at 12:29 PM, Brian G. Huber wrote:

Thanks for the response - but II does not seem to work as an aggregate function - for example - if I use:

SELECT groupid, sum(numeric_field), ||(text_field) FROM table GROUP BY groupid

I get

ERROR: Unable to identify a prefix operator '||' for type 'text'
You may need to add parentheses or an explicit cast

Therefore, I think II concatenates two text columns, but does not aggregate. It would seem this is fairly common but there is no text aggregation function listed in 6.14 Aggregate Functions!!!
----- Original Message -----

From: Godshall Michael
To: 'Brian G. Huber' ; pgsql-novice@postgresql.org
Sent: Tuesday, September 09, 2003 12:10 PM
Subject: Re: [NOVICE] Concatenating string fields with GROUP BY clause

Concatenate is done with || in postgresql

-----Original Message-----
From: Brian G. Huber [mailto:brianghuber@yahoo.com]
Sent: Tuesday, September 09, 2003 12:02 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] Concatenating string fields with GROUP BY clause

Hi -

I am trying to concatenate a text field in a query with a group by clause, similar to a sum() function on a numeric value - for example:

SELECT groupid, sum(numeric_field), ???(text_field) FROM table GROUP BY groupid

but I cannot find a function that will concatenate the text fields. Any comments appreciated!

TIA,BGH

Re: Concatenating string fields with GROUP BY clause

From

"Gaetano Mendola"

Date:

10 September 2003, 02:47:23

"Godshall Michael" <Michael_Godshall@gmachs.com> wrote:
> Concatenate is done with  ||   in postgresql

This is the SQL standard  way to concatenate string.


Regards
Gaetano Mendola

Re: Concatenating string fields with GROUP BY clause

From

stork@vnet.hu (Vajda Gabor)

Date:

14 September 2003, 20:08:20

Dear Brian!

You need to write an aggregate like this:

  CREATE AGGREGATE textcat_all(
      basetype    = text,
      sfunc       = textcat,
      stype       = text,
      initcond    = ''
  );

You can use this new aggregate in your query. For example:

  SELECT partner.name, textcat_all(phones.number || ', ')
      FROM partner LEFT JOIN phones ON partner.id = phones.partner_id
      GROUP BY partner.name;

Bye,

Gabor Vajda

brianghuber@yahoo.com ("Brian G. Huber") wrote in message news:<001b01c376f4$124dc410$6400a8c0@bghmobile>...
> Hi -
>
> I am trying to concatenate a text field in a query with a group by clause,
> similar to a sum() function on a numeric value - for example:
>
> SELECT groupid, sum(numeric field), ???(text field) FROM table GROUP BY gro
> upid
>
> but I cannot find a function that will concatenate the text fields. Any com
> ments appreciated!
>
> TIA,BGH
> --