I have 3 tables in mysql:
t_group
:
id | groupid
--------------
1 group1
2 group2
t_user
:
id | userid | groupid
----------------------
1 user1 group1
2 user2 group1
3 user3 group2
4 user4 group2
5 user5 group2
t_order
:
id | orderid | userid
---------------------
1 order1 user1
2 order2 user2
3 order3 user2
4 order4 user4
I like to get the number of users and the number of orders by group. My query is as follows:
SELECT t_group.groupid, COUNT(t_user.id) as num_user, COUNT(t_order.userid) as num_order
FROM `t_group`
left join t_user on t_group.groupid=t_user.groupid
left join t_order on t_order.userid=t_user.userid
GROUP by t_group.groupid
But I got the weird result:
------------------------------
groupid | num_user | num_order
------------------------------
group1 3 3
group2 3 1
The actual number of users in group1 is 2, but it shows 3. Isn't it possible to get my desired result in a single join query?
CodePudding user response:
You can try this approach to achieve desired result:
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;
CodePudding user response:
try with inner join:
SELECT t_group.groupid, COUNT(t_user.id) as num_user, COUNT(t_order.userid) as num_order FROM `t_group`
INNER JOIN t_user ON t_group.groupid=t_user.groupid
INNER JOIN t_order ON t_order.userid=t_user.userid
GROUP by t_group.groupid