CODEWITHSUNDEEP
mysqlsql
Samuel Shifterovich
Samuel Shifterovich

Reputation: 271

Get the number of users that belong to a group and the number of orders that belong to the group through its users

I have a user_groups table, a users table and an orders table.

The relationship between them is this: users.group_id, orders.user_id.

I'm trying to get the amount of users that belong to each group, and the amount of orders that belong to each group through its users.

My code:

select user_groups.*, count(users.id) as user_count, count(orders.id) as order_count

from user_groups

left join users on user_groups.id=users.group_id
left join orders on users.id=orders.user_id

group by user_groups.id

Expected output:

id | user_count | order_count
1  | 5          | 67
2  | 1          | 1

Actual output:

enter image description here

The amount of orders should be 5, not 71:

enter image description here

Upvotes: 0

Views: 28

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Use count(distinct):

select ug.*, count(distinct u.id) as user_count, count(distinct o.id) as order_count    
from user_groups ug left join
     users u
     on ug.id = u.group_id left join
     orders o
     on u.id = o.user_id
group by ug.id;

count(id) counts the number of non-NULL values. You apparently want to count the number of different values, which is what distinct does.

Upvotes: 2

Related Questions