The very much simplified example works fine, but my real SQL is a bit more complex:
select gp_name, count(*) as pat_count, sum(emis_number in(select emis_number from diabetics)) as diab_count from patients where emis_number in(select emis_number from diabetics) and emis_number in(select emis_number from on_non_insulin) or emis_number in(select emis_number from on_insulin) group by gp_name order by pat_count asc patients is a table, equivalent to Table1 in the simplified example and diabetics, on_non_insulin and on_insulin are views, holding subgroups of the id column in the table patients. Emis_number is equivalent to id in the simplified example and gp_name is equivalent to type in the simplified example. The above SQL works, but gives too low counts for diab_count . Any suggestion how to adapt? 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
