"Yuzem" <[email protected]> wrote in
message news:[email protected]
> Suppose that have 2 columns: folders and tags:
> ~/Music|classic,rock
> ~/Music|classic,rock
> ~/Pictures|art,photos
> ~/Pictures|art,photos
> ~/Pictures|art,photos
>
> To know the folder count I do:
> sqlite3 test.db "select folder, count(folders) from t1 group by
> folder"
>
> Returns:
> ~/Music|2
> ~/Pictures|3
>
> How can I do the same for tags using only sqlite to get this:
> art|3
> classic|2
> photos|3
> rock|2
Consider normalizing your database. Split into two tables - folders and
tags - with a one-to-many relationship between them. Then the solution
would be trivial.
If you insist on keeping the database as is:
select tag, count(*)
from
(select replace(rtrim(tags,'abcdefghijklmnopqrstuvwxyz'),',','') as tag
from t1
union
select replace(ltrim(tags,'abcdefghijklmnopqrstuvwxyz'),',','') as tag
from t1) as alltags
join t1 on t1.tags like '%' || tag || '%'
group by tag;
This monstrosity gives the correct answer in your specific example, but
it relies on there being exactly two tags per folder.
> Another question, is there any way to get max count for all grouped
> columns in one single consult?
> It should return something like this:
> 2|4
>
> Meaning that the first column has 2 unique values (~/Music and
> ~/Pictures) and the second column has 4 (art, classic. photos and
> rock)
The second column doesn't have 4 unique values - it has two:
'classic,rock' and 'art,photos'. A comma in the middle of a field value
has no special meaning in SQL - it doesn't magically turn one value into
two. Anyway:
select
(select count(*) from (select distinct folder from t1)),
(select count(*) from (
select replace(rtrim(tags,'abcdefghijklmnopqrstuvwxyz'),',','')
from t1
union
select replace(ltrim(tags,'abcdefghijklmnopqrstuvwxyz'),',','')
from t1));
Same caveat applies.
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
