Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t_group(Id int 
,groupid varchar (30)
);
CREATE TABLE t_user (Id int 
,userid varchar (30)
,groupid varchar (30));
CREATE TABLE t_order (Id int 
,orderid  varchar (30)
,userid varchar (30)
);
INSERT INTO t_group VALUES (1,'group1' );
INSERT INTO t_group VALUES (2,'group2' );
1 rows affected

1 rows affected

SELECT * FROM t_group ;


id
groupid


1
group1

2
group2


INSERT INTO t_user VALUES (1 ,   'user1',   'group1'  );
INSERT INTO t_user VALUES (2 ,   'user2',   'group1'  );
INSERT INTO t_user VALUES (3 ,   'user3','group2'  );
INSERT INTO t_user VALUES (4 ,   'user4',    'group2'  );
INSERT INTO t_user VALUES (5  ,  'user5',   'group2' );
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

SELECT * FROM t_user ;


id
userid
groupid


1
user1
group1

2
user2
group1

3
user3
group2

4
user4
group2

5
user5
group2


INSERT INTO t_order VALUES (1  ,  'order1' ,   'user1'  );
INSERT INTO t_order VALUES (2  ,  'order2',    'user2'  );
INSERT INTO t_order VALUES (3   , 'order3',    'user2'  );
INSERT INTO t_order VALUES (4   , 'order4',   'user4'  );
1 rows affected

1 rows affected

1 rows affected

1 rows affected

SELECT * FROM t_order;


id
orderid
userid


1
order1
user1

2
order2
user2

3
order3
user2

4
order4
user4


select count(distinct (u.userid)) count_user, u.groupid
from t_group g
inner join t_user u on u.groupid= g.groupid
group by u.groupid


count_user
groupid


2
group1

3
group2


select count(distinct (o.orderid))count_order, u.groupid
from t_group g
inner join t_user u on u.groupid= g.groupid
inner join t_order o on u.userid = o.userid 
group by u.groupid;


count_order
groupid


3
group1

1
group2


select temp1.groupid,temp1.count_user,temp2.count_order from 
(select count(distinct (u.userid)) count_user, u.groupid
from t_group g
inner join t_user u on u.groupid= g.groupid
group by u.groupid
) temp1
left join
(select count(distinct (o.orderid))count_order, u.groupid
from t_group g
inner join t_user u on u.groupid= g.groupid
inner join t_order o on u.userid = o.userid 
group by u.groupid) temp2
on temp1.groupid= temp2.groupid
order by temp1.groupid;


groupid
count_user
count_order


group1
2
3

group2
3
1
id	userid	groupid
1	user1	group1
2	user2	group1
3	user3	group2
4	user4	group2
5	user5	group2