counting customer orders

[Simon]

I have a query that counts how many orders have been placed for each
post code area

If a customer places 10 order and he lives in the BS postcode its
shows 10 sales in that postcode area

how can i set it not to count the number of orders but just to count
it as one customer in that area that has placed one or more orders


Thanks

 

[Ken Sheridan]

It won't be fast, but here's an example using the sample Northwind database
that counts the number of customers per shipping postcode:

SELECT DISTINCT ShipPostalCode,
(SELECT COUNT(*)
FROM Customers
WHERE EXISTS
(SELECT *
FROM Orders AS O2
WHERE O2.CustomerID = Customers.CustomerID
AND O2.ShipPostalCode = O1.ShipPostalCode))
AS CustomerCount
FROM Orders AS O1
WHERE ShipPostalCode IS NOT NULL;

The way it works is that the outer query returns the distinct ShipPostalCode
values from Orders and includes in its SELECT clause a subquery which counts
the rows in Customers where a second subquery returns at least one row. The
second subquery is correlated both with the outer query (on ShipPostalCode)
and with the first subquery (on CustomerID). It’s the double correlation
which slows things down.

Ken Sheridan
Stafford, England