WHERE filtering happens before aggregates, so you can't use it on an aggregate column, only a HAVING clause works for that. Or you need to make it a sub-query and surround it with another select to get the where
so something like... select c, group_concat(p) as P from t group by c having P = '10'; ...or if it doesn't recognize P, then select c, group_concat(p) as P from t group by c having group_concat(p) = '10'; -----Original Message----- From: sqlite-users [mailto:[email protected]] On Behalf Of Mark Wagner Sent: Tuesday, December 04, 2018 11:34 AM To: SQLite mailing list Subject: Re: [sqlite] how to Prior to my original message I was playing around with group concat (even though I know the order is considered arbitrary) but found I couldn't put a where clause on that column. Is there anything wrong with this code? sqlite> select c, group_concat(p) as P from t group by c; c P ---------- ---------- 1 10,11,12 2 11,12 3 10 BUT... select c, group_concat(p) as P from t where P='10,11,12' group by c ; <no results> select c, group_concat(p) as P from t where P='10' group by c ; <no results> On Tue, Dec 4, 2018 at 6:04 AM R Smith <[email protected]> wrote: > I've mixed up the adding orders to make sure they have no affect on the > outcome - and in the final results examples I've left all the columns so > you can see what is going on, but you of course need only one of the > columns in your desired output. > > > CREATE TABLE t (id integer primary key, c, p); > INSERT INTO t VALUES(1, 1,11); > INSERT INTO t VALUES(2, 1,12); > INSERT INTO t VALUES(3, 1,10); > INSERT INTO t VALUES(4, 2,11); > INSERT INTO t VALUES(5, 2,12); > INSERT INTO t VALUES(6, 3,10); > > -- Base Query: > WITH SETS(PSet, PContent, PCount) AS ( > SELECT c, GROUP_CONCAT(p,','), COUNT(*) FROM (SELECT * FROM t ORDER > BY c,p) GROUP BY c > ) > SELECT * > FROM SETS > ; > > -- PSet | PContent | PCount > -- ------------ | -------- | ------ > -- 1 | 10,11,12 | 3 > -- 2 | 11,12 | 2 > -- 3 | 10 | 1 > > > -- Example one - finding the set that contains all of 10,11,12 and > nothing else: > WITH SETS(PSet, PContent, PCount) AS ( > SELECT c, GROUP_CONCAT(p,','), COUNT(*) FROM (SELECT * FROM t ORDER > BY c,p) GROUP BY c > ) > SELECT * > FROM SETS > WHERE PContent = '10,11,12' > ; > > -- PSet | PContent | PCount > -- ------------ | ------------ | ------------ > -- 1 | 10,11,12 | 3 > > > -- Example two: Finding any set that contains 11,12: > WITH SETS(PSet, PContent, PCount) AS ( > SELECT c, GROUP_CONCAT(p,','), COUNT(*) FROM (SELECT * FROM t ORDER > BY c,p) GROUP BY c > ) > SELECT * > FROM SETS > WHERE PContent LIKE '%11,12%' > ; > > -- PSet | PContent | PCount > -- ------------ | -------- | ------ > -- 1 | 10,11,12 | 3 > -- 2 | 11,12 | 2 > > > > On 2018/12/04 6:17 AM, Mark Wagner wrote: > > Given a table with two columns, A and B, with no constraints what would > be > > the best way to query for those values of A such that there are > > corresponding values of B in a specified set. > > > > For example, given this data, below, and ignoring the primary key, I > would > > want the following results: > > > > for p values 10,11,12 ==> 1 > > for p values 11,12 ==> 2 > > for p values 10 ==> 3 > > > > For all other "input" we should get no result/null/whatever. > > > > CREATE TABLE t (id integer primary key, c, p); > > > > INSERT INTO t VALUES(1, 1,10); > > > > INSERT INTO t VALUES(2, 1,11); > > > > INSERT INTO t VALUES(3, 1,12); > > > > INSERT INTO t VALUES(4, 2,11); > > > > INSERT INTO t VALUES(5, 2,12); > > > > INSERT INTO t VALUES(6, 3,10); > > > > > > For all other "input" we should get no result/null/whatever. > > > > I can concoct a query based on the "input" like this but it seems like > > there must be a better way? > > > > SELECT DISTINCT c as C FROM t WHERE > > EXISTS (SELECT c FROM t AS x WHERE x.p = 10) > > AND EXISTS (SELECT c FROM t AS x WHERE x.p = 11) > > AND EXISTS (SELECT c FROM t AS x WHERE x.p = 12) > > > > AND (SELECT count(*) FROM t as x WHERE C = x.c) = 3; > > _______________________________________________ > > sqlite-users mailing list > > [email protected] > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
