Re: [sqlite] how to use group_concat uniquely
Igor Tandetnik wrote: > > On 1/19/2011 12:51 PM, Noah Hart wrote: >>> select E, replace(group_concat(distinct T), ',', ';'), >>> replace(group_concat(distinct P), ',', ';') from MyTable; >> >>Ahhh >> the DISTINCT keyword was what I'm missing. However, this may be a bug, >> when I use group_concat(DISTINCT T, ';') I get the error message >> DISTINCT aggregates must have exactly one argument > > That's why I had to muck around with replace(). The syntax only allows > DISTINCT keyword in aggregate functions taking exactly one parameter. > group_concat defaults to comma as a separator when called with one > parameter. > -- > Igor Tandetnik > Well, I went and read the page on the Aggregate Functions, and it is very clear in the documentation. Thanks again, Noah -- View this message in context: http://old.nabble.com/how-to-use-group_concat-uniquely-tp30712025p30716460.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to use group_concat uniquely
On 1/19/2011 12:51 PM, Noah Hart wrote: >> select E, replace(group_concat(distinct T), ',', ';'), >> replace(group_concat(distinct P), ',', ';') from MyTable; > >Ahhh > the DISTINCT keyword was what I'm missing. However, this may be a bug, > when I use group_concat(DISTINCT T, ';') I get the error message > DISTINCT aggregates must have exactly one argument That's why I had to muck around with replace(). The syntax only allows DISTINCT keyword in aggregate functions taking exactly one parameter. group_concat defaults to comma as a separator when called with one parameter. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to use group_concat uniquely
On 1/19/2011 12:19 PM, Noah Hart wrote: > I am having difficulty trying to return data in a very compact form. Here > is > a simple example that will explain the problem: > > I have a table with column E, T and P containing data such as > A, 1, R > A, 1, S > A, 2, R > A, 2, S > > Trying the query > select E, group_concat(T,';'), group_concat(P,';') give me the expected > results > A 1;1;2;2 R;S;R;S > > However, I would like to create a query will return the results > > A 1;2 R;S select E, replace(group_concat(distinct T), ',', ';'), replace(group_concat(distinct P), ',', ';') from MyTable; Wouldn't work if values in T or P contain commas. Here's a more verbose query that doesn't have this limitation (but is likely much slower): select E, (select group_concat(T, ';') from (select distinct T from MyTable where E=AllE.E)), (select group_concat(P, ';') from (select distinct P from MyTable where E=AllE.E)) from (select distinct E from MyTable) AllE; -- Igor Tandetnik ___ Ahhh the DISTINCT keyword was what I'm missing. However, this may be a bug, when I use group_concat(DISTINCT T, ';') I get the error message DISTINCT aggregates must have exactly one argument Noah -- View this message in context: http://old.nabble.com/how-to-use-group_concat-uniquely-tp30712025p30712310.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to use group_concat uniquely
On 1/19/2011 12:19 PM, Noah Hart wrote: > I am having difficulty trying to return data in a very compact form. Here is > a simple example that will explain the problem: > > I have a table with column E, T and P containing data such as > A, 1, R > A, 1, S > A, 2, R > A, 2, S > > Trying the query > select E, group_concat(T,';'), group_concat(P,';') give me the expected > results > A 1;1;2;2 R;S;R;S > > However, I would like to create a query will return the results > > A 1;2 R;S select E, replace(group_concat(distinct T), ',', ';'), replace(group_concat(distinct P), ',', ';') from MyTable; Wouldn't work if values in T or P contain commas. Here's a more verbose query that doesn't have this limitation (but is likely much slower): select E, (select group_concat(T, ';') from (select distinct T from MyTable where E=AllE.E)), (select group_concat(P, ';') from (select distinct P from MyTable where E=AllE.E)) from (select distinct E from MyTable) AllE; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to use group_concat uniquely
On Wed, Jan 19, 2011 at 09:19:54AM -0800, Noah Hart wrote: > Any ideas? You have two columns to sub-group by independently, as it were. You need correlated sub-queries to get that done: sqlite> SELECT f1.e, (SELECT group_concat(f2.t, ';') ...> FROM foo f2 WHERE f1.e = f2.e GROUP BY f2.p), (SELECT group_concat(f2.p, ';') ...> FROM foo f2 WHERE f1.e = f2.e GROUP BY f2.t) FROM foo f1 GROUP BY f1.e; A|1;2|R;S B|1;2|R;S sqlite> Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how to use group_concat uniquely
I am having difficulty trying to return data in a very compact form. Here is a simple example that will explain the problem: I have a table with column E, T and P containing data such as A, 1, R A, 1, S A, 2, R A, 2, S Trying the query select E, group_concat(T,';'), group_concat(P,';') give me the expected results A 1;1;2;2 R;S;R;S However, I would like to create a query will return the results A 1;2 R;S Any ideas? Noah Hart -- View this message in context: http://old.nabble.com/how-to-use-group_concat-uniquely-tp30712025p30712025.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
