Re: 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:


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

 

Arial0000,0000,FFFFSELECT
groupid, sum(numeric_field), ||(text_field) FROM table GROUP BY groupid

 

Arial0000,0000,FFFFI
get

 

Arial0000,0000,FFFFERROR: 
Unable to identify a prefix operator '||' for type 'text'

 You may need to add parentheses or an explicit cast

 

Arial0000,0000,FFFFTherefore,
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:
1999,1999,FFFFGodshall Michael

To: 1999,1999,FFFF'Brian
G. Huber' ;
1999,1999,FFFFpgsql-novice@postgresql.org

Sent: Tuesday, September 09, 2003 12:10 PM

Subject: Re: [NOVICE] Concatenating string fields with
GROUP BY clause


Arial0000,0000,FFFFConcatenate
is done with  ||   in postgresql


Times New Roman-----Original
Message-----

From: Brian G. Huber [mailto:brianghuber@yahoo.com]

Sent: Tuesday, September 09, 2003 12:02 PM

To:
1999,1999,FFFFpgsql-novice@postgresql.org

Subject: [NOVICE] Concatenating
string fields with GROUP BY clause


Arial0000,0000,FFFFHi -

 

Arial0000,0000,FFFFI
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:

 

Arial0000,0000,FFFFSELECT
groupid, sum(numeric_field), ???(text_field) FROM table GROUP BY
groupid

 

Arial0000,0000,FFFFbut
I cannot find a function that will concatenate the text fields. Any
comments appreciated!

 

Arial0000,0000,FFFFTIA,BGH


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
>