Group by counting 2 different sub-groups

classic Classic list List threaded Threaded
16 messages Options
Reply | Threaded
Open this post in threaded view
|

Group by counting 2 different sub-groups

Bart Smissaert
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?

RBS
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Group by counting 2 different sub-groups

Igor Tandetnik-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Group by counting 2 different sub-groups

Bart Smissaert
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Group by counting 2 different sub-groups

Bart Smissaert
In reply to this post by Igor Tandetnik-2
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Group by counting 2 different sub-groups

Igor Tandetnik-2
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 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

Just in case you are not aware, AND has higher precedence than OR. Your query does ( (Diabetics AND Non-Insulin) OR Insulin ) as opposed to ( Diabetics AND (Non-Insulin OR Insulin) ). Apologies if you knew that and really meant the former.

> The above SQL works, but gives too low counts for diab_count .

Show sample data, the result you expect, and the result you observe instead.
--
Igor Tandetnik


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Group by counting 2 different sub-groups

Bart Smissaert
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 <[hidden email]> wrote:

> 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 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
>
> Just in case you are not aware, AND has higher precedence than OR. Your
> query does ( (Diabetics AND Non-Insulin) OR Insulin ) as opposed to (
> Diabetics AND (Non-Insulin OR Insulin) ). Apologies if you knew that and
> really meant the former.
>
> > The above SQL works, but gives too low counts for diab_count .
>
> Show sample data, the result you expect, and the result you observe
> instead.
> --
> Igor Tandetnik
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Group by counting 2 different sub-groups

Simon Slavin-3
On 25 Nov 2018, at 12:59am, Bart Smissaert <[hidden email]> 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 this, please thin your database down to just enough data to demonstrate your problem.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Group by counting 2 different sub-groups

Bart Smissaert
OK, will describe the data as done before.

RBS

On Sun, Nov 25, 2018 at 1:08 AM Simon Slavin <[hidden email]> wrote:

> On 25 Nov 2018, at 12:59am, Bart Smissaert <[hidden email]>
> 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 this, please thin your database down to just enough data to demonstrate
> your problem.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Group by counting 2 different sub-groups

Igor Tandetnik-2
In reply to this post by Bart Smissaert
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 diabetics)" is true for every row, guaranteed by the WHERE clause.
--
Igor Tandetnik


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Group by counting 2 different sub-groups

Bart Smissaert
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 are smaller sub_groups.

RBS

On Sun, Nov 25, 2018 at 1:41 AM Igor Tandetnik <[hidden email]> wrote:

> 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 diabetics)" is
> true for every row, guaranteed by the WHERE clause.
> --
> Igor Tandetnik
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Group by counting 2 different sub-groups

Bart Smissaert
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 <[hidden email] 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.
> Diabetics holds the largest number of ID and the ID's in on_non_insulin
> and on_insulin are smaller sub_groups.
>
> RBS
>
> On Sun, Nov 25, 2018 at 1:41 AM Igor Tandetnik <[hidden email]> wrote:
>
>> 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
>> diabetics)" is true for every row, guaranteed by the WHERE clause.
>> --
>> Igor Tandetnik
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Group by counting 2 different sub-groups

Igor Tandetnik-2
In reply to this post by Bart Smissaert
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Group by counting 2 different sub-groups

Bart Smissaert
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Group by counting 2 different sub-groups

Bart Smissaert
In reply to this post by Igor Tandetnik-2
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Group by counting 2 different sub-groups

R Smith-2

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 (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

But this should work:

SELECT *, drug_count / diab_count as percentage
   FROM (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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Group by counting 2 different sub-groups

Bart Smissaert
Ah, yes, of course.
Thanks.

RBS

On Sun, Nov 25, 2018 at 12:24 PM R Smith <[hidden email]> 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:
> >
> > 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
>
> But this should work:
>
> SELECT *, drug_count / diab_count as percentage
>    FROM (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
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users