how to use group_concat uniquely

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

how to use group_concat uniquely

Noah Hart
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
Reply | Threaded
Open this post in threaded view
|

Re: how to use group_concat uniquely

Nicolas Williams-2
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how to use group_concat uniquely

Igor Tandetnik
In reply to this post by Noah Hart
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how to use group_concat uniquely

Noah Hart

<quote author="Igor Tandetnik">
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
Reply | Threaded
Open this post in threaded view
|

Re: how to use group_concat uniquely

Igor Tandetnik
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how to use group_concat uniquely

Noah Hart

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