Lists: | pgsql-novice |
---|
From: | Godshall Michael <Michael_Godshall(at)gmachs(dot)com> |
---|---|
To: | "'Brian G(dot) Huber'" <brianghuber(at)yahoo(dot)com>, pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Concatenating string fields with GROUP BY clause |
Date: | 2003-09-09 17:10:10 |
Message-ID: | A596FA3368757645AF862C701495CA0001B4453B@hor1mspmx01.gmachs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
Concatenate is done with || in postgresql
-----Original Message-----
From: Brian G. Huber [mailto:brianghuber(at)yahoo(dot)com]
Sent: Tuesday, September 09, 2003 12:02 PM
To: pgsql-novice(at)postgresql(dot)org
Subject: [NOVICE] Concatenating string fields with GROUP BY clause
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
groupid
but I cannot find a function that will concatenate the text fields. Any
comments appreciated!
TIA,BGH
From: | "Brian G(dot) Huber" <brianghuber(at)yahoo(dot)com> |
---|---|
To: | "Godshall Michael" <Michael_Godshall(at)gmachs(dot)com>, <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Concatenating string fields with GROUP BY clause |
Date: | 2003-09-09 17:29:49 |
Message-ID: | 001701c376f7$fa6af8a0$6400a8c0@bghmobile |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
Thanks for the response - but II does not seem to work as an aggregate function - for example - if I use:
SELECT groupid, sum(numeric_field), ||(text_field) FROM table GROUP BY groupid
I get
ERROR: Unable to identify a prefix operator '||' for type 'text'
You may need to add parentheses or an explicit cast
Therefore, I think II concatenates two text columns, but does not aggregate. It would seem this is fairly common but there is no text aggregation function listed in 6.14 Aggregate Functions!!!
----- Original Message -----
From: Godshall Michael
To: 'Brian G. Huber' ; pgsql-novice(at)postgresql(dot)org
Sent: Tuesday, September 09, 2003 12:10 PM
Subject: Re: [NOVICE] Concatenating string fields with GROUP BY clause
Concatenate is done with || in postgresql
-----Original Message-----
From: Brian G. Huber [mailto:brianghuber(at)yahoo(dot)com]
Sent: Tuesday, September 09, 2003 12:02 PM
To: pgsql-novice(at)postgresql(dot)org
Subject: [NOVICE] Concatenating string fields with GROUP BY clause
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 groupid
but I cannot find a function that will concatenate the text fields. Any comments appreciated!
TIA,BGH
From: | Jeffrey Melloy <jmelloy(at)visualdistortion(dot)org> |
---|---|
To: | "Brian G(dot) Huber" <brianghuber(at)yahoo(dot)com> |
Cc: | "Godshall Michael" <Michael_Godshall(at)gmachs(dot)com>, <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Concatenating string fields with GROUP BY clause |
Date: | 2003-09-09 17:52:21 |
Message-ID: | 5CCD3A86-E2EE-11D7-A539-000393C78AC0@visualdistortion.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
I think what he's trying to do is this:
a | b | g
1 | sun | 1
2 | shoe | 1
3 | pea | 2
4 | door | 2
he gets this as the response:
group, sum, concat
1, 3, sunshoe
2, 7, peadoor
So a "concatenation aggregate"
That being said, I can't think of any simple way to do it in SQL.
Jeff
On Tuesday, September 9, 2003, at 12:29 PM, Brian G. Huber wrote:
> Thanks for the response - but II does not seem to work as an aggregate
> function - for example - if I use:
>
> SELECT groupid, sum(numeric_field), ||(text_field) FROM table GROUP BY
> groupid
>
> I get
>
> ERROR: Unable to identify a prefix operator '||' for type 'text'
> You may need to add parentheses or an explicit cast
>
> Therefore, I think II concatenates two text columns, but does not
> aggregate. It would seem this is fairly common but there is no text
> aggregation function listed in 6.14 Aggregate Functions!!!
> ----- Original Message -----
>
> From: Godshall Michael
> To: 'Brian G. Huber' ; pgsql-novice(at)postgresql(dot)org
> Sent: Tuesday, September 09, 2003 12:10 PM
> Subject: Re: [NOVICE] Concatenating string fields with GROUP BY clause
>
> Concatenate is done with || in postgresql
>
> -----Original Message-----
> From: Brian G. Huber [mailto:brianghuber(at)yahoo(dot)com]
> Sent: Tuesday, September 09, 2003 12:02 PM
> To: pgsql-novice(at)postgresql(dot)org
> Subject: [NOVICE] Concatenating string fields with GROUP BY clause
>
> 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 groupid
>
> but I cannot find a function that will concatenate the text fields.
> Any comments appreciated!
>
> TIA,BGH
>
From: | "Brian G(dot) Huber" <brianghuber(at)yahoo(dot)com> |
---|---|
To: | "Jeffrey Melloy" <jmelloy(at)visualdistortion(dot)org> |
Cc: | "Godshall Michael" <Michael_Godshall(at)gmachs(dot)com>, <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Concatenating string fields with GROUP BY clause |
Date: | 2003-09-09 18:02:54 |
Message-ID: | 004101c376fc$9843eb50$6400a8c0@bghmobile |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
Thanks for the example Jeff - Well said - that is what I'm trying to do.
----- Original Message -----
From: Jeffrey Melloy
To: Brian G. Huber
Cc: Godshall Michael ; pgsql-novice(at)postgresql(dot)org
Sent: Tuesday, September 09, 2003 12:52 PM
Subject: Re: [NOVICE] Concatenating string fields with GROUP BY clause
I think what he's trying to do is this:
a | b | g
1 | sun | 1
2 | shoe | 1
3 | pea | 2
4 | door | 2
he gets this as the response:
group, sum, concat
1, 3, sunshoe
2, 7, peadoor
So a "concatenation aggregate"
That being said, I can't think of any simple way to do it in SQL.
Jeff
On Tuesday, September 9, 2003, at 12:29 PM, Brian G. Huber wrote:
Thanks for the response - but II does not seem to work as an aggregate function - for example - if I use:
SELECT groupid, sum(numeric_field), ||(text_field) FROM table GROUP BY groupid
I get
ERROR: Unable to identify a prefix operator '||' for type 'text'
You may need to add parentheses or an explicit cast
Therefore, I think II concatenates two text columns, but does not aggregate. It would seem this is fairly common but there is no text aggregation function listed in 6.14 Aggregate Functions!!!
----- Original Message -----
From: Godshall Michael
To: 'Brian G. Huber' ; pgsql-novice(at)postgresql(dot)org
Sent: Tuesday, September 09, 2003 12:10 PM
Subject: Re: [NOVICE] Concatenating string fields with GROUP BY clause
Concatenate is done with || in postgresql
-----Original Message-----
From: Brian G. Huber [mailto:brianghuber(at)yahoo(dot)com]
Sent: Tuesday, September 09, 2003 12:02 PM
To: pgsql-novice(at)postgresql(dot)org
Subject: [NOVICE] Concatenating string fields with GROUP BY clause
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 groupid
but I cannot find a function that will concatenate the text fields. Any comments appreciated!
TIA,BGH
From: | Jason Hihn <jhihn(at)paytimepayroll(dot)com> |
---|---|
To: | "Brian G(dot) Huber" <brianghuber(at)yahoo(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Concatenating string fields with GROUP BY clause |
Date: | 2003-09-09 19:57:45 |
Message-ID: | NGBBLHANMLKMHPDGJGAPCENHCMAA.jhihn@paytimepayroll.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
I was just working on a similar problem. I resulted in to doing it in code.
HAVING would be the avenue, but it is just like a where clause and you can't
say HAVING previous record's g field=this record's g field. :-( You must
sort first by the g field then just spin through while the g fields are the
same.
If anyone knows how to to it within SQL only, I'd appreciate knowing too.
-----Original Message-----
From: pgsql-novice-owner(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org]On Behalf Of Brian G. Huber
Sent: Tuesday, September 09, 2003 2:03 PM
To: Jeffrey Melloy
Cc: Godshall Michael; pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Concatenating string fields with GROUP BY clause
Thanks for the example Jeff - Well said - that is what I'm trying to do.
----- Original Message -----
From: Jeffrey Melloy
To: Brian G. Huber
Cc: Godshall Michael ; pgsql-novice(at)postgresql(dot)org
Sent: Tuesday, September 09, 2003 12:52 PM
Subject: Re: [NOVICE] Concatenating string fields with GROUP BY clause
I think what he's trying to do is this:
a | b | g
1 | sun | 1
2 | shoe | 1
3 | pea | 2
4 | door | 2
he gets this as the response:
group, sum, concat
1, 3, sunshoe
2, 7, peadoor
So a "concatenation aggregate"
That being said, I can't think of any simple way to do it in SQL.
Jeff
On Tuesday, September 9, 2003, at 12:29 PM, Brian G. Huber wrote:
Thanks for the response - but II does not seem to work as an aggregate
function - for example - if I use:
SELECT groupid, sum(numeric_field), ||(text_field) FROM table GROUP BY
groupid
I get
ERROR: Unable to identify a prefix operator '||' for type 'text'
You may need to add parentheses or an explicit cast
Therefore, I think II concatenates two text columns, but does not
aggregate. It would seem this is fairly common but there is no text
aggregation function listed in 6.14 Aggregate Functions!!!
----- Original Message -----
From: Godshall Michael
To: 'Brian G. Huber' ; pgsql-novice(at)postgresql(dot)org
Sent: Tuesday, September 09, 2003 12:10 PM
Subject: Re: [NOVICE] Concatenating string fields with GROUP BY clause
Concatenate is done with || in postgresql
-----Original Message-----
From: Brian G. Huber [mailto:brianghuber(at)yahoo(dot)com]
Sent: Tuesday, September 09, 2003 12:02 PM
To: pgsql-novice(at)postgresql(dot)org
Subject: [NOVICE] Concatenating string fields with GROUP BY clause
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 groupid
but I cannot find a function that will concatenate the text fields.
Any comments appreciated!
TIA,BGH
From: | "Gaetano Mendola" <mendola(at)bigfoot(dot)com> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Cc: | "Godshall Michael" <Michael_Godshall(at)gmachs(dot)com> |
Subject: | Re: Concatenating string fields with GROUP BY clause |
Date: | 2003-09-10 02:44:01 |
Message-ID: | 006001c37745$6c66a1a0$15720b3e@mm.eutelsat.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-novice |
"Godshall Michael" <Michael_Godshall(at)gmachs(dot)com> wrote:
> Concatenate is done with || in postgresql
This is the SQL standard way to concatenate string.
Regards
Gaetano Mendola