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

[sqlite] Why not text mode for file from .output?

I notice that the file written by the CLI shell’s .output command, on the Windows platform, does not have CRLF line ends. From the code in v3.25.0 and v3.26.0 (pre-release), it appears that the file named as the .output parameter is opened in binary mode. (bTxtMode is left at its initialized v

Re: [sqlite] PRIMARY KEY not enforcing NOT NULL, WITHOUT ROWID default - suggestions

Digital Dog wrote: > > PRAGMA default_without_rowid = on > To make all tables created while the directive is in use the WITHOUT ROWID > tables. .. > > PRAGMA enforce_not_null_on_primary_key = on > For WITHOUT ROWID tables it would be a no-op, but for rowid tables it would > restore correct behav