Thanks for that! Very nice and simple. (note there is a superfluous comma after id_count_view1)
RBS On Sun, Nov 25, 2018 at 12:17 AM Igor Tandetnik <i...@tandetnik.org> wrote: > On 11/24/2018 6:59 PM, Bart Smissaert wrote: > > Have a table called Table1 like this: > > > > id Type > > ---------- > > 1 a > > 2 b > > 3 a > > 4 c > > 5 a > > 6 b > > 7 c > > 8 c > > 9 b > > 10 a > > > > Table create is this: > > CREATE TABLE [Table1]([id] INTEGER PRIMARY KEY, [Type] TEXT) > > > > Then there are 2 views, created like this > > CREATE VIEW View1 as select id from Table1 where id < 8 > > CREATE VIEW View2 as select id from Table1 where id < 5 > > > > I can run a SQL like this: > > > > select type, count(id) as id_count_view2 from Table1 > > where id in(select id from view2) > > group by type > > > > and that will give me: > > > > Type id_count_view2 > > ---------------------------- > > a 2 > > b 1 > > c 1 > > > > But I would like in 2 columns the counts of both views, so I would like > > this output: > > > > Type id_count_view2 id_count_view1 > > ------------------------------------------------- > > a 2 3 > > b 1 2 > > c 1 2 > > > > How should this be done? > > > select type, > sum(id in (select id from view2)) id_count_view2, > sum(id in (select id from view1)) id_count_view1, > from Table1 > group by type; > > -- > Igor Tandetnik > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
