Re: [GENERAL] How to get array of unique array values across rows?
That worked perfectly. Thanks a lot! On Tue, Mar 5, 2013 at 12:49 PM, ChoonSoo Park wrote: > Try this one. > > select X.client_id, array_agg(X.color) > from (select distinct client_id, unnest(service_codes) as color >from foo) X > group by X.client_id; > > > On Tue, Mar 5, 2013 at 3:28 PM, Ken Tanzer wrote: > >> I have a field containing a set of codes in a varchar array, each tied to >> a person. >> >> client_id | integer >> | >> service_codes | character varying(10)[] | >> >> I'm trying to query this info so that I can get the list (presumably in >> an array) of all the values in this array, across all the records for a >> client. So that if a person has two records, one with ORANGE and BLUE, and >> one with BLUE and GREEN, I could end up with a list of ORANGE, BLUE and >> GREEN. >> >> I had hopes for: >> >> SELECT client_id,array_agg(service_codes) FROM foo GROUP BY client_id; >> >> But was rebuffed with "ERROR: could not find array type for data type >> character varying[]" >> >> There's probably an easy answer for this, but it's completely escaping >> me. Any help appreciated. Thanks. >> >> Ken >> >> -- >> AGENCY Software >> A data system that puts you in control >> 100% Free Software >> *http://agency-software.org/* >> ken.tan...@agency-software.org >> (253) 245-3801 >> >> Subscribe to the mailing >> list >> to >> learn more about AGENCY or >> follow the discussion. >> > > -- AGENCY Software A data system that puts you in control 100% Free Software *http://agency-software.org/* ken.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list to learn more about AGENCY or follow the discussion.
Re: [GENERAL] How to get array of unique array values across rows?
Try this one. select X.client_id, array_agg(X.color) from (select distinct client_id, unnest(service_codes) as color from foo) X group by X.client_id; On Tue, Mar 5, 2013 at 3:28 PM, Ken Tanzer wrote: > I have a field containing a set of codes in a varchar array, each tied to > a person. > > client_id | integer > | > service_codes | character varying(10)[] | > > I'm trying to query this info so that I can get the list (presumably in an > array) of all the values in this array, across all the records for a > client. So that if a person has two records, one with ORANGE and BLUE, and > one with BLUE and GREEN, I could end up with a list of ORANGE, BLUE and > GREEN. > > I had hopes for: > > SELECT client_id,array_agg(service_codes) FROM foo GROUP BY client_id; > > But was rebuffed with "ERROR: could not find array type for data type > character varying[]" > > There's probably an easy answer for this, but it's completely escaping me. > Any help appreciated. Thanks. > > Ken > > -- > AGENCY Software > A data system that puts you in control > 100% Free Software > *http://agency-software.org/* > ken.tan...@agency-software.org > (253) 245-3801 > > Subscribe to the mailing > list > to > learn more about AGENCY or > follow the discussion. >
[GENERAL] How to get array of unique array values across rows?
I have a field containing a set of codes in a varchar array, each tied to a person. client_id | integer | service_codes | character varying(10)[] | I'm trying to query this info so that I can get the list (presumably in an array) of all the values in this array, across all the records for a client. So that if a person has two records, one with ORANGE and BLUE, and one with BLUE and GREEN, I could end up with a list of ORANGE, BLUE and GREEN. I had hopes for: SELECT client_id,array_agg(service_codes) FROM foo GROUP BY client_id; But was rebuffed with "ERROR: could not find array type for data type character varying[]" There's probably an easy answer for this, but it's completely escaping me. Any help appreciated. Thanks. Ken -- AGENCY Software A data system that puts you in control 100% Free Software *http://agency-software.org/* ken.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list to learn more about AGENCY or follow the discussion.
