Q:

MySQL query to count user orders by type

I have this 3 tables: users, orders and order_item.
When a user can have a order, and the order item can be event only, membership only or both, and so 2 line will be written to the order_items
users tables
id | user_id   | name  | phone
---|-----------|-------|------
1  | 123456789 | Jon   | 555-55555
2  | 123456780 | Alice | 555-6666

orders tables
id | user_id | user_uid  | user_info
---|---------|-----------|----------
1  | 1       | 123456789 | bla
2  | 2       | 123456780 | foo
3  | 2       | 123456780 | foo

order_items table
id  | order_id | order_type | price
--- | -------- | ---------- | ------
1   | 1        | membership | 70
2   | 1        | event      | 200
3   | 2        | event      | 300
4   | 3        | membership | 70

The relationship is like this,
order_items.order_id -> orders.id
orders.user_id -> users.id
orders.user_uid -> users.user_id

I'm looking for a query which will produce this type of output,
user_id   | name   | count_membership   | count_events   | total_orders
--------  | ------ | ------------------ | -------------- | --------------
123456789 | Jon    | 1                  | 1              | 1
123456780 | Alice  | 1                  | 1              | 2

I like to count the total orders a user have, and count how many of each of item he have. in the end I like to filter out all users where count_membership = 0 
Thanks in advance,

A:

You can get the expected result set by using some conditional aggregation
select u.user_id,
u.name,
sum(oi.membership_count) membership_count,
sum(oi.event_count) event_count,
count(o.id) total_orders
from users u
join orders o on u.id = o.user_id
join (
  select order_id,
  sum(order_type = 'membership') membership_count,
  sum(order_type = 'event') event_count
  from order_items 
  group by order_id
) oi on o.id = oi.order_id
group by u.user_id,u.name

Demo

