CODEWITHSUNDEEP
sqlsql-server
Polyfun
Polyfun

Reputation: 9639

Return count for each distinct

I have the following tables:

ORDER

OrderId
-------
Order1
Order2
Order3

CUSTOMERORDER

OrderId  CustomerId
-------  ----------
Order1   Cust1
Order1   Cust2
Order2   Cust3
Order3   Cust1

The query

select distinct count(*) as CustomersPerOrder
from CustomerOrder
group by CustomerOrder.OrderId
order by CustomersPerOrder

returns

CustomersPerOrder
-----------------
1
2

That is, some orders have 1 customer, some have 2. I also want to know how many orders have 1 customer, and how many have 2:

CustomersPerOrder  OrdersCount
-----------------  -----------
1                  2
2                  1

That is, 2 orders (OrdersCount) have 1 customer (CustomersPerOrder), and 1 order (OrdersCount) has 2 customers (CustomersPerOrder).

Upvotes: 1

Views: 61

Answers (2)

Brett Schneider
Brett Schneider

Reputation: 4103

put away the distinct and count the occurences of and group the CustomersPerOrder

with counter as (
 select count(customerid) as CustomersPerOrder
 from CustomerOrder
 group by CustomerOrder.OrderId
)
select CustomersPerOrder, COUNT(*) as OrdersCount 
from counter
group by CustomersPerOrder

Upvotes: 2

Amy B
Amy B

Reputation: 110071

SELECT CustomersPerOrder, COUNT(*)
FROM
(
SELECT COUNT(*) as CustomersPerOrder
from CustomerOrder
group by CustomerOrder.OrderId
) sub
GROUP BY CustomersPerOrder

Upvotes: 2

Related Questions