After adding 2 other views it looks better: select gp_name, sum(emis_number in (select emis_number from diabetics)) as diab_count, sum(emis_number in (select emis_number from diab_on_non_insulin) or emis_number in (select emis_number from diab_on_insulin)) as drug_count from patients group by gp_name order by diab_count asc
Is it possible to use the aliases diab_count and drug_count directly in a fourth column to show the percentage? This doesn't work: select gp_name, sum(emis_number in (select emis_number from diabetics)) as diab_count, sum(emis_number in (select emis_number from diab_on_non_insulin) or emis_number in (select emis_number from diab_on_insulin)) as drug_count, drug_count / diab_count as percentage from patients group by gp_name order by diab_count asc It will give: no such column drug_count RBS On Sun, Nov 25, 2018 at 2:47 AM Igor Tandetnik <i...@tandetnik.org> wrote: > On 11/24/2018 8:51 PM, Bart Smissaert wrote: > > Ok, in the first count column I would like the grouped counts for > patients > > in the views on_non_insulin or on_insulin and > > in the second count column I would like the grouped counts for patients > the > > view diabetics. > > Well, you already know the technique for this. > > select gp_name, > sum(emis_number in (select emis_number from diabetics), > sum(emis_number in (select emis_number from on_non_insulin) OR > emis_number in (select emis_number from on_insulin)) > from patients group by gp_name; > > -- > 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
