CODEWITHSUNDEEP
mysql
Werner
Werner

Reputation: 1847

mysql: list number of orders all customers made

I have 2 simple tables like customers and orders where a customer can have 0 to n orders. I would like to have a list like: 10 customers made 0 orders 4 customers made 1 order 5 customers made 2 orders 2 customers made 3 orders ... and so on. So far I have:

SELECT customers.id FROM customers
LEFT JOIN orders ON orders.customerId = customers.id
HAVING COUNT(orders.id) = 1;

which lists me all customers who made 1 order, but as I said I need a list with all possebilities.

Upvotes: 0

Views: 60

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

SELECT COUNT(*), t.orderCount
FROM
(
    SELECT u.id, COUNT(o.id) AS orderCount
    FROM users u LEFT JOIN orders o
        ON u.id = o.userId
    GROUP BY u.id
) t
GROUP BY t.orderCount

The inner query will give each user and his order total:

user total
  1   2
  2   3
  3   2
  4   3

The outer query does a GROUP BY this total to count the number of customers who share the total:

# customers  total
     2         2
     2         3

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

You can use aggregate function and group bu

SELECT ifnull(count(orders.id),0), users.id FROM users
LEFT JOIN orders ON orders.userId = users.id
group by users.id

Upvotes: 0

Related Questions