Trouble with getting a distinct count

  • Thread starter Thread starter Simon Jester
  • Start date Start date
S

Simon Jester

I am having a lot of trouble figuring this out, I am embarassed to
say, and was hoping one of you kind folks could steer me in the right
direction.

I have a table with the following fields: CustomerID (a unique
number), ServiceID(a unique Number for each service delivered),
ServiceTypeID (A unique ID number assigned to each service),
ServiceName, and ServiceDate. Every time a customer receives one of
several services it is recorded in the table. A customer can receive
any of the services numerous times or not at all.

What I can't figure out how to do is to come up with a distinct count
of customers for each service. For example, how many distinct
customers received ServicetTypeIDs 100, 110, & 120? When I try I keep
coming up with results that count each customer every time they
receive a service. I am stymied as to how to do this and if anyone
can give me any direction, I would be forever grateful.

Thanks much!
SJ
 
Hi Simon,

Maybe one of the query gurus will post a method of doing this in a single
query (probably with a sub-query), but I find this sort of thing easier to
accomplish in two steps.

The following query (I'll name it qryServiceType_Customers) will give all
combinations of servicetypes and customers:
SELECT ServiceTypeID, CustomerID
FROM YourTableName
GROUP BY ServiceTypeID, CustomerID;

Then you can use this to get the count of distinct customers for each
servicetype:
SELECT ServiceTypeID, Count(ServiceTypeID) AS DistinctCustomers
FROM qryServiceType_Customers
GROUP BY ServiceTypeID;

HTH,

Rob
 
Back
Top