SQL query - single text value from group by

Hi

I am trying to work out if this is possible in a select query

I have a group by query which could result in several rows, what I want
to do is do a text equivalent of a sum() eg:

SELECT sum(inv_id),date,cust
from invoice
group by date,cust

Is there any way to get to a single concatenated inv_id field with just
one row?

thanks

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

re: SQL query - single text value from group by

mike wrote:[color=blue]
> Hi
>
> I am trying to work out if this is possible in a select query
>
> I have a group by query which could result in several rows, what I want
> to do is do a text equivalent of a sum() eg:
>
> SELECT sum(inv_id),date,cust
> from invoice
> group by date,cust
>
> Is there any way to get to a single concatenated inv_id field with just
> one row?[/color]

Yes, you can define your own aggregate. For example, I did this the
other day:

/*
Custom aggregate
This aggregate is so we can aggregate text into paragraph blocks
*/
CREATE OR REPLACE FUNCTION join_paras(text, text) RETURNS text AS
'
SELECT CASE
WHEN ($1 = '''') THEN $2
ELSE $1 || ''\n'' || $2
END;
' LANGUAGE 'SQL' IMMUTABLE;

CREATE AGGREGATE agg_paras (sfunc1=join_paras, basetype=text,
stype1=text, initcond1='');

Note I defined my own text-concatenation function because I wanted to
insert newlines between each block of text. If you just wanted joined
text you could use the built-in textcat()

Full specs of create aggregate are in the manuals.

HTH
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

re: SQL query - single text value from group by

On Wed, 2004-09-08 at 17:13 +0100, Richard Huxton wrote:[color=blue]
> mike wrote:[color=green]
> > Hi
> >
> > I am trying to work out if this is possible in a select query
> >
> > I have a group by query which could result in several rows, what I want
> > to do is do a text equivalent of a sum() eg:
> >
> > SELECT sum(inv_id),date,cust
> > from invoice
> > group by date,cust
> >
> > Is there any way to get to a single concatenated inv_id field with just
> > one row?[/color]
>
> Yes, you can define your own aggregate. For example, I did this the
> other day:
>
> /*
> Custom aggregate
> This aggregate is so we can aggregate text into paragraph blocks
> */
> CREATE OR REPLACE FUNCTION join_paras(text, text) RETURNS text AS
> '
> SELECT CASE
> WHEN ($1 = '''') THEN $2
> ELSE $1 || ''\n'' || $2
> END;
> ' LANGUAGE 'SQL' IMMUTABLE;
>
> CREATE AGGREGATE agg_paras (sfunc1=join_paras, basetype=text,
> stype1=text, initcond1='');
>
> Note I defined my own text-concatenation function because I wanted to
> insert newlines between each block of text. If you just wanted joined
> text you could use the built-in textcat()
>
> Full specs of create aggregate are in the manuals.
>
> HTH[/color]


thanks - as sods law dictates I found a solution just after I posted
along the same lines, after a couple of hours of fruitless googling.



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings