Re: [sqlite] Group by counting 2 different sub-groups

Ah, yes, of course. Thanks. RBS On Sun, Nov 25, 2018 at 12:24 PM R Smith wrote: > > On 2018/11/25 1:50 PM, Bart Smissaert wrote: > > Is it possible to use the aliases diab_count and drug_count directly in a > > fourth column to show the percentage? > > No. > > > This doesn't work: > > > > selec

Re: [sqlite] Group by counting 2 different sub-groups

On 2018/11/25 1:50 PM, Bart Smissaert wrote: Is it possible to use the aliases diab_count and drug_count directly in a fourth column to show the percentage? No. This doesn't work: select gp_name, sum(emis_number in (select emis_number from diabetics)) as diab_count, sum(emis_number in (sele

Re: [sqlite] Group by counting 2 different sub-groups

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_n

Re: [sqlite] Group by counting 2 different sub-groups

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 di

Re: [sqlite] Group by counting 2 different sub-groups

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

Re: [sqlite] Group by counting 2 different sub-groups

So, in other words in the second count column I would like the result of this: select p.gp_name as GP, count(d.emis_number) as pat_count from patients p inner join diabetics d on(p.emis_number = d.emis_number) group by GP order by pat_count asc RBS On Sun, 25 Nov 2018, 01:51 Bart Smissaert Ok,

Re: [sqlite] Group by counting 2 different sub-groups

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. Diabetics holds the largest number of ID and the ID's in on_non_insulin and on_insulin

Re: [sqlite] Group by counting 2 different sub-groups

On 11/24/2018 7:59 PM, Bart Smissaert wrote: Thanks, was aware, but the SQL was indeed wrong as posted and should have brackets around the 2 or conditions. In this case, as far as I can tell you should end up with diab_count equal to pat_count, since "emis_number in(select emis_number from dia

Re: [sqlite] Group by counting 2 different sub-groups

OK, will describe the data as done before. RBS On Sun, Nov 25, 2018 at 1:08 AM Simon Slavin wrote: > On 25 Nov 2018, at 12:59am, Bart Smissaert > wrote: > > > Could I post a little demo SQLite file? Not sure now if this is allowed > as an attachment. > > This mailing list strips attachments.

Re: [sqlite] Group by counting 2 different sub-groups

On 25 Nov 2018, at 12:59am, Bart Smissaert wrote: > Could I post a little demo SQLite file? Not sure now if this is allowed as an > attachment. This mailing list strips attachments. You could use the SQLite CLI tool to .dump the database as a text file, and paste it into a message. If you do

Re: [sqlite] Group by counting 2 different sub-groups

Thanks, was aware, but the SQL was indeed wrong as posted and should have brackets around the 2 or conditions. Corrected now. Could I post a little demo SQLite file? Not sure now if this is allowed as an attachment. That would be easiest. RBS On Sun, Nov 25, 2018 at 12:52 AM Igor Tandetnik wrote

Re: [sqlite] Group by counting 2 different sub-groups

On 11/24/2018 7:44 PM, Bart Smissaert wrote: 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

Re: [sqlite] Group by counting 2 different sub-groups

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_numbe

Re: [sqlite] Group by counting 2 different sub-groups

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 wrote: > On 11/24/2018 6:59 PM, Bart Smissaert wrote: > > Have a table called Table1 like this: > > > > id Type > > -- > > 1 a > > 2 b > >

Re: [sqlite] Group by counting 2 different sub-groups

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 selec

[sqlite] Group by counting 2 different sub-groups

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