help with 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!

 

[jbenson001]

There may be a better way, but I would select your first set of results into a temp table. Then use that temp table to get your final count.

Lets say you select into #temp:

Select OrdersMade, Count(OrdersMade) As NumberOfCustomers
From #Temp
Group By OrdersMade
Order By OrdersMade

Jim

 

[SQLDenis]

what does this return?

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

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Walid]

SELECT A.TotalOrders AS NumberOfOrders, COUNT(A.CustomerID) AS NumberOfCustomers
FROM (SELECT CustomerID, COUNT(CustomerID) AS TotalOrders
		FROM Orders
		GROUP BY CustomerID) A
GROUP BY A.TotalOrders

The inner query will get the number of orders per each customer in the database

Jack – 13 Orders
Joe - 1 Order
Sue – I Order

Etc

The outer query will count the customers grouping them by their order number, this will give you what you want. Hope this will help


Walid Magd (MCP)

The primary challenge of every software development team is to engineer the illusion of simplicity in the face of essential complexity.
-Grady Booch

 

[Walid]

Sorry Denis
We posted at the same time I think.

Walid Magd (MCP)

The primary challenge of every software development team is to engineer the illusion of simplicity in the face of essential complexity.
-Grady Booch