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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
