Postgres Pro
Downloads
Home   >   mailing lists

Re: Concatenating string fields with GROUP BY clause - Mailing list pgsql-novice

From Jason Hihn
Subject Re: Concatenating string fields with GROUP BY clause
Date
Msg-id NGBBLHANMLKMHPDGJGAPCENHCMAA.jhihn@paytimepayroll.com
Whole thread Raw
In response to Re: Concatenating string fields with GROUP BY clause  ("Brian G. Huber" <brianghuber@yahoo.com>)
List pgsql-novice
Tree view
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 -----
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

pgsql-novice by date:

Previous
From: Oliver Fromme
Date:
Subject: Re: Modifying pg_shadow?
Next
From: Jason Hihn
Date:
Subject: Re: Modifying pg_shadow?