Selecting groups of repeat orders by using aggregate functions

[againstTheWind]

I am trying to determine the number of repeat customers we have.

The data is an ORDERS table where using:

select count(CustomerID) as OrdersMade
from Orders
group by customerID

will provide the total number of orders for each customer. Example:

CustomerID OrdersMade
5456 2
4547 1
5667 5

However I need to know how many customers made 3, 4, 5, 6, etc... orders. Thus the date would look like this:

OrdersMade NumberOfCustomers
1 99999999
2 56789
3 23000
4 1200

etc....

Any ideas?

Thanks a bunch!

 

[lespaul]

maybe something like:

select A.OrdersMade, Count(A.*) As NumberOfCustomers From (select CustomerID, count(CustomerID) as OrdersMade
from Orders
group by customerID) As A
GROUP BY A.OrdersMade

what database are you working in? You may want to post in the forum dedicated to that database.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[1oldfoxman]

create view Interim_Step as
select count(CustomerID) as OrdersMade
from Orders
group by customerID;

select Orders_Made, count(customer_id)
from Interim_Step
group by Orders_Made
order by Orders_Made;

This may vary depending on the SQL flavor (especially the view creation.). It could also be an interim table that gets created instead of a view.

If you have to have each and every possible Orders_Made line appear even if count is zero, you'll need a different approach.

hth
Dennis