Yes, thanks, got this worked out now.
Had to make 2 alterations:
1. missing closing bracket after from diabetic)
2. needed to make sure that the grouped drug counts were only in patients
with diabetes
So, this works:
select gp_name,
sum(emis_number in (select emis_number from diabetics)) as diab_count,
sum(emis_number in (select emis_number from on_non_insulin) and
emis_number in(select emis_number from diabetics) OR
emis_number in (select emis_number from on_insulin) and
emis_number in(select emis_number from diabetics)) as drug_count
from patients group by gp_name
order by diab_count asc
I am running this on an android phone and there are some difficulties
making the app understand the datatypes of the select columns in this case
(I am coding in B4A) but that is a completely different problem.
Very helpful to know this solution of:
sum(field in (select field from object))
and there are lots of these not well known solutions that don't show in my
SQL text books.
Would you know any books that show all these options?
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
