Skip site navigation (1) Skip section navigation (2)

Re: Concatenating string fields with GROUP BY clause

From: stork(at)vnet(dot)hu (Vajda Gabor)
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Concatenating string fields with GROUP BY clause
Date: 2003-09-13 20:10:11
Message-ID: db7789b.0309131210.625da358@posting.google.com (view raw or flat)
Thread:
Lists: pgsql-novice
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
> --

In response to

pgsql-novice by date

Next:From: Jim Byrne (NTL)Date: 2003-09-13 20:12:23
Subject: Postgresql 'backend closed the channelunexpectedly' - how tore-install?
Previous:From: marcin.gilDate: 2003-09-13 09:52:26
Subject: Re: operation logging

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group