Hello I am trying to group multiple customer orders into buckets in SQL, the output should look something like it does below. Do I have to use a case statement to group them?

Table1 looks like:

Edit: # of customers meaning if CustomerID 2 had 2 orders he/she would be of the in the bucket of #of orders of 2.

Output should be something like this?

My code so far is:

select count(*) CustomerID 
FROM Table1
GROUP BY CustomerID;

CodePudding user response：

I believe what you want to do is get the count of orders by customer, first, via aggregation. Then get the count of customers by order count from that query.

SELECT count(*) as count_of_customers, count_of_orders
FROM 
    (
        SELECT customerid, count(*) as count_of_orders
        FROM your_table
        GROUP BY customerid
    ) sub
GROUP BY count_of_orders
ORDER BY count_of_orders

CodePudding user response：

Use a double aggregation:

SELECT COUNT(*) AS num_customers, cnt AS num_orders
FROM
(
    SELECT CustomerID, COUNT(*) AS cnt
    FROM Table1
    GROUP BY CustomerID
) t
GROUP BY cnt;

The inner subquery finds the number of orders for each customer. The outer query then aggregates by number of orders and finds out the number of customers having each number of orders.

CodePudding user response：

If you want to sort your tables and your users depending on the number of orders they made, this query should work:

SELECT CustomerID, COUNT(CustomerID) as NbOrder 
FROM Table1 
GROUP BY(NbOrder)