Received: from ece.rice.edu (ece.rice.edu [128.42.4.34]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5IGgwa71059 for ; Mon, 18 Jun 2001 12:42:58 -0400 (EDT) (envelope-from reedstrm@rice.edu) Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by ece.rice.edu (Postfix) with ESMTP id 52AB668A66 for ; Mon, 18 Jun 2001 11:42:53 -0500 (CDT) Received: from reedstrm by wallace.ece.rice.edu with local (Exim 3.22 #1 (Debian)) id 15C25q-0004Ff-00 for ; Mon, 18 Jun 2001 11:41:38 -0500 Date: Mon, 18 Jun 2001 11:41:37 -0500 From: "Ross J. Reedstrom" To: pgsql-sql@postgresql.org Subject: Re: sum of string columns, why ? Message-ID: <20010618114137.J14955@rice.edu> Mail-Followup-To: "Ross J. Reedstrom" , pgsql-sql@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.3.18i X-Archive-Number: 200106/258 X-Sequence-Number: 3516 On Wed, Jun 06, 2001 at 06:13:18PM -0300, Marcos Vaz - ( NewAge Group ) wrote: > I have one table with thwo columns, user and text, why sum all the text > fields of the one user ? I assume you mean 'how' not 'why'? You don't give a lot of detail, like what you mean by 'sum all the text'. One interpretation is 'count the number of text entries for one user', as so: select user, count(text) from some_table group by user; If instead you mean concatenate all the text and return it as one big string, that's a bit trickier. You need a custom aggregate function that concatenates strings. Here's one I use to generate comma delimited concatenations: REATE AGGREGATE catenate ( BASETYPE = text, SFUNC = commacat, STYPE = text, INITCOND = '' ); CREATE FUNCTION "commacat" (text,text) RETURNS text AS ' begin if $1 <> '''' then return $1 || '', '' || $2 ; else return $2; end if; end; ' LANGUAGE 'plpgsql'; It works as so: select user,catenate(text) from some_table group by user; You'll want to change the helper function to not inserting commas, however. Ross