Count Distinct

[SASHA_D]

Hi Everyone!,

I have a table with the follwing;
ShopID, CustomerID, Month, Item_ref, Product Category

There are about one thousand shops.
The CustomerID field is only unique per shop.
I.e. all transactions for customer ID '002' in shop 1 will be for the same customer, but customer ID '002' could appear in another shop and will be a different customer,
I want to be able to bring back a list of distinct customers, the number of items they have had and the product categories.

Has anyone got any ideas how I do this? I presume I would need to do a count distinct or something??

thanks!,

Sasha

 

[mresann]

You will actually need two queries, one to get a count of items the customers purchased, and another one to give you the categories. I'm not sure if you want a count of the categories, or a return of the types of categories.

IN any case, here is the query I came up with

SELECT Table1.ShopID, Table1.CustomerID, Count(Table1.Item_ref) AS CountOfItem_ref
FROM Table1
GROUP BY Table1.ShopID, Table1.CustomerID
ORDER BY Table1.ShopID, Table1.CustomerID;

This should give you a start.

 

[SASHA_D]

Thanks for the quick rely!,

I will give this a try.

Regarding categories-at the moment I just want to have seperate lists of unique customers for each category, so I guess I can just add the category field into the query.

What I ultimately want to do though is get a breakdown of customers and the combinations of products categories they use.
E.g. Customer 1 has bought product category A and C
Customer 2 has bought category A only
Customer 3 has bought category C and category D

There are 8 categories, so there are quite alot of different combinations-around 255 I believe!
The management also want to somehow track customers because there is a belief that a customer will start using just one product category, but will then move on to using several categories.

Any further help on this would be much appreciated-It really has got me stuck!!!

thanks again,

Sasha.