I use || to count distinct on multiple fields. It works for this particular example. But I'm not sure if it is robust. Would you please let me know if there is any better way?
#!/usr/bin/env bash rm -f main.db sqlite3 main.db <<EOF create table test (id integer primary key, value1 text, value2 text); insert into test (value1,value2) values('x', 'x'); insert into test (value1,value2) values('x', 'y'); insert into test (value1,value2) values('y', 'x'); insert into test (value1,value2) values('y', 'y'); insert into test (value1,value2) values('x', 'x'); insert into test (value1,value2) values('x', 'y'); insert into test (value1,value2) values('y', 'x'); insert into test (value1,value2) values('y', 'y'); insert into test (value1,value2) values('z', 'z'); .mode column .headers on .echo on select distinct value1, value2 from test; select count(distinct value1||value2) from test; EOF -- Regards, Peng _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users