On Fri, Jun 13, 2008 at 09:20:41AM -0700, gtxy20 scratched on the wall: > > Hello all; > > Any help, directions, or thoughts on this is much apprecaited. > > Let's say I have the following table: > > ID Value > 1 a > 1 a
These dup records aren't cool and cause some additional issues below, like the need for a distinct sub-select for no other reason that removing them. > 1 b > 2 c > 2 a > 3 a > 3 c > 3 b sqlite> create table x (id integer, value char); sqlite> insert into x values (1, 'a'); sqlite> insert into x values (1, 'a'); sqlite> insert into x values (1, 'b'); sqlite> insert into x values (2, 'a'); sqlite> insert into x values (2, 'c'); sqlite> insert into x values (3, 'a'); sqlite> insert into x values (3, 'b'); sqlite> insert into x values (3, 'c'); > Is there a quick way in SQLite that will let me build a new table so that it > is now represented where the ValueCombination is sorted such as: > > ID ValueCombination > 1 a > 2 a,c > 3 a,b,c sqlite> select id, group_concat(value) from x group by id; 1|a,a,b 2|a,c 3|a,b,c OK, not quite the same thing... You also forgot the 1/b record. That makes it hard to understand what, exactly, you want, since you're output examples are incorrect. > After this is done can I build a table from this table for the counts > associated to the Values in the ValueCombination field such as: > > Value Count > a 3 > b 1 > c 2 sqlite> select v, count(i) from ...> (select distinct value as v, id as i from x) ...> group by v; a|3 b|2 c|2 Again, you forgot the 1/b record in your example output. > After this I would like to build a table again from the ValueCombination > table to count the instances of the ValueCombination such as: > > ValueCombination Count > a 1 > a,b,c 1 > a,c 1 sqlite> select v, count(*) from ...> (select id, group_concat(value) as v from x group by id) ...> group by v; a,a,b|1 a,b,c|1 a,c|1 If you want to put these in tables, rather than just queries, select into a table. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
