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

Section 6.4

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

-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org]On Behalf Of Brian G. Huber
Sent: Tuesday, September 09, 2003 1:02 PM
To: pgsql-novice(at)postgresql(dot)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

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(at)yahoo(dot)com ("Brian G. Huber") wrote in message news:<001b01c376f4$124dc410$6400a8c0(at)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
> --