Count Unique records

[SASHA_D]

Hi everyone,

I have a table which has the following 3 fields:-
Customer_ID
Shop_ID
Month

I need a count by month for each shop for the number of unique customers.
There is a complication in that the customer ID field is not unique from shop to shop. E.g. there could be a customer ID number 255 in shop 1, a different customer in shop 2 could also have the ID number of 255.
A combination of customerID and shop ID will be unique.
The data set is also very large-20 million rows!

Can anyone help???

Many thanks,

Sasha

 

[Len Boorman]

Think you will need two queries

Query 1
Basically
Select Distinct Shop, Customer,Month

This will give you the distinct listing and ignore any additional record for any month where Customer and Shop combination are duplicated.

Query 2
If you have a summation query selecting from Query 1
Shop Shop (Again I know) and Month and then set Count against the second Shop I think you will get

Shop Count(Representing Unique Customers) and Month

Have the feeling I may not have explained that very well.

Get back to me if I have confused you.

L