CODEWITHSUNDEEP
mysqlsql
gommb
gommb

Reputation: 1117

SQL count number of people by number of orders

I need help getting a certain outcome from sql.
users table:

userid | name
-------|-----------
1      | Bob Robert
2      | Steve Smith
3      | Willard Henegar
4      | Max Rockwell
5      | Marion Paley
6      | Marcus Delisle

orders table:

orderid | userid
--------|-------
1       |1
2       |1
3       |1
4       |2
5       |3
6       |2
7       |4
8       |4
9       |5
10      |4
11      |4
12      |1

wanted outcome

numOrders | numPeople
----------|----------
0         |1
1         |2
2         |1
3         |0
4         |2

so basically i want to show how many people have no orders, how many people have 1 order, 2 orders, etc.

I tried to figure it out and came up with this:

SELECT
 Count(orders.orderid) AS numOrders,
 Count(users.userid)   AS numPeople 
FROM   users 
       LEFT JOIN orders 
              ON users.userid = orders.userid 
GROUP  BY numOrders

but this gives my an error that i can't group on numOrders.

Any suggestions on how i could achieve this?

Upvotes: 2

Views: 3311

Answers (3)

Oliver
Oliver

Reputation: 26

If you also want Users without any orders, you can do the following:

select NumOrders, count(*) as NumPeople
from (
    select u.UserId, count(*) as NumOrders
    from users u
    left join orders o on o.UserId = o.UserId
group by o.UserId
) t
group by t.NumOrders

You could also do:

select NumOrders, count(*) as NumPeople
from (
    select
    u.UserId, 
    (select count(*) from orders o where o.UserId = u.UserId) as NumOrders
from users u
) t
group by t.NumOrders

As Gordon mentioned, having a number table would help. If you know that no one will have more than say, 1,000 orders, and you want show one row for each number 0 - 1000, you could do something like this:

select number as NumPeople, isnull(p.NumOrders, 0) as NumOrders
from master..spt_values v
left join 
(
    select t.NumOrders, count(*) as NumPeople
    from ( select
            u.UserId, 
            (select count(*) from order o where o.UserId = u.UserId) as NumOrders
    from users u) t
    group by NumOrders
) p on p.NumOrders = v.number
where type = 'P'
and number <= 1000

The above would work for SQL Server. I'm not sure if MySQL has a master values table, but otherwise, you could easily create a number table and swap that out.

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

select count(userid), numorders 
from(
SELECT
Count(*) AS numOrders,
userid 
from orders 
group by userid ) t
group by numorders

Orders table would be sufficient as you just need the count of orders and users.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You want a double histogram. This is the easiest way:

select cnt, count(*), min(userid), max(userid)
from (select u.userid, count(o.userid) as cnt
      from users u left join
           orders o
           on u.userid = o.userid
      group by u.userid
     ) u
group by cnt;

This will not, however, give you the row with 0 count. That requires a bit more effort. It helps if you have a numbers table to fill in the missing values.

Upvotes: 2

Related Questions