pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Concatenate aggregate?
3+ messages / 2 participants
[nested] [flat]

* Concatenate aggregate?
@ 2002-08-06 08:03 Julian Scarfe <julian.scarfe@ntlworld.com>
  2002-08-06 08:28 ` Re: Concatenate aggregate? Archibald Zimonyi <archie@netg.se>
  0 siblings, 1 reply; 3+ messages in thread

From: Julian Scarfe @ 2002-08-06 08:03 UTC (permalink / raw)
  To: pgsql-sql

I'd like to concatenate text from one field that has been returned by a
grouped query.

e.g.

create table test (id int, thetext text);
CREATE
playj=> insert into test values (1, 'Hello number 1');
INSERT 697997 1
playj=> insert into test values (2, 'Hello number 2');
INSERT 697998 1
playj=> insert into test values (1, 'Hello again number 1');
INSERT 697999 1
playj=> select * from test;

 id |       thetext
----+----------------------
  1 | Hello number 1
  2 | Hello number 2
  1 | Hello again number 1
(3 rows)

I can do:

playj=> select id, max(thetext) from test group by id;

 id |      max
----+----------------
  1 | Hello number 1
  2 | Hello number 2
(2 rows)

But what I'd like to do is something like:

playj=> select id, concat(thetext, ' -- ') from test group by id;

 id |      concat
----+----------------
  1 | Hello number 1 -- Hello again number 1
  2 | Hello number 2
(2 rows)

[Ordering is unimportant here.]

Any pointers to useful starting points please?

Thanks

Julian Scarfe






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Concatenate aggregate?
  2002-08-06 08:03 Concatenate aggregate? Julian Scarfe <julian.scarfe@ntlworld.com>
@ 2002-08-06 08:28 ` Archibald Zimonyi <archie@netg.se>
  2002-08-06 08:37   ` Re: Concatenate aggregate? Julian Scarfe <julian.scarfe@ntlworld.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Archibald Zimonyi @ 2002-08-06 08:28 UTC (permalink / raw)
  To: Julian Scarfe <julian.scarfe@ntlworld.com>; +Cc: pgsql-sql



One way is to create your own aggregate function. See the reference manual
under CREATE AGGREGATE.

On Tue, 6 Aug 2002, Julian Scarfe wrote:

> I'd like to concatenate text from one field that has been returned by a
> grouped query.
>
> e.g.
>
> create table test (id int, thetext text);
> CREATE
> playj=> insert into test values (1, 'Hello number 1');
> INSERT 697997 1
> playj=> insert into test values (2, 'Hello number 2');
> INSERT 697998 1
> playj=> insert into test values (1, 'Hello again number 1');
> INSERT 697999 1
> playj=> select * from test;
>
>  id |       thetext
> ----+----------------------
>   1 | Hello number 1
>   2 | Hello number 2
>   1 | Hello again number 1
> (3 rows)
>
> I can do:
>
> playj=> select id, max(thetext) from test group by id;
>
>  id |      max
> ----+----------------
>   1 | Hello number 1
>   2 | Hello number 2
> (2 rows)
>
> But what I'd like to do is something like:
>
> playj=> select id, concat(thetext, ' -- ') from test group by id;
>
>  id |      concat
> ----+----------------
>   1 | Hello number 1 -- Hello again number 1
>   2 | Hello number 2
> (2 rows)
>
> [Ordering is unimportant here.]
>
> Any pointers to useful starting points please?
>
> Thanks
>
> Julian Scarfe
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Concatenate aggregate?
  2002-08-06 08:03 Concatenate aggregate? Julian Scarfe <julian.scarfe@ntlworld.com>
  2002-08-06 08:28 ` Re: Concatenate aggregate? Archibald Zimonyi <archie@netg.se>
@ 2002-08-06 08:37   ` Julian Scarfe <julian.scarfe@ntlworld.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Julian Scarfe @ 2002-08-06 08:37 UTC (permalink / raw)
  To: Archibald Zimonyi <archie@netg.se>; +Cc: pgsql-sql

From: "Archibald Zimonyi" <archie@netg.se>

> One way is to create your own aggregate function. See the reference manual
> under CREATE AGGREGATE.

Thank you.

http://www.postgresql.org/idocs/index.php?sql-createaggregate.html

And indeed Chris's LIST() aggregate does almost exactly what I want.  I
don't think it was there last time I tried to figure out CREATE AGGREGATE.
The examples on that page are very helpful.

Julian







^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2002-08-06 08:37 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-06 08:03 Concatenate aggregate? Julian Scarfe <julian.scarfe@ntlworld.com>
2002-08-06 08:28 ` Archibald Zimonyi <archie@netg.se>
2002-08-06 08:37   ` Julian Scarfe <julian.scarfe@ntlworld.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox