Concatenate aggregate?

From: "Julian Scarfe" <julian(dot)scarfe(at)ntlworld(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Concatenate aggregate?
Date: 2002-08-06 08:03:21
Message-ID: 005201c23d1f$bb4f6ba0$0500a8c0@Wilbur
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Archibald Zimonyi 2002-08-06 08:28:37 Re: Concatenate aggregate?
Previous Message Robert Treat 2002-08-05 21:40:44 Re: VACUUM not doing its job?