SELECT DISTINCT and COUNT DISTINCT

[paulwilliams]

Hi All

Thanks for taking the time to read my post. This is my problem.

I have a table called SalesData that contains the following fields:

- CustomerName
- ProdCode
- Qty
- Turnover
- SalesDate

This table is simply a list of the products a customer has bought, how many they have bought and how much they spent on those products in any given month.

I have a related table called ProdsToGroups, containing the following fields:

- ProductCode
- ProductGroup

This is a list of all possible product codes and the associated product group for each (there are 2500 product codes and 42 product groups). This table is related to SalesData by [SalesData].[ProdCode] = [ProdsToGroups].[ProdCode].

What I would like to know is how many customers have bought a product from each product group each month and produce a table where each row is the product group, each column is a month and each cell is the unique number of customers who bought a product from that group in that month.

I have used SELECT DISTINCT to produce the list of product groups but I can't then return a number of unique customers who bought from that group. I assume the use of COUNT DISTINCT but i'm having problems putting the query together.

Any help would be greatly appriciated.

Thanks
Paul

 

[GanzPopp]

Try this:

SELECT CustomerName, ProdCode, SUM(Qty) FROM SalesData 
LEFT JOIN ProdsToGroups ON [SalesData].[ProdCode] = [ProdsToGroups].[ProdCode] 
GROUP BY CustomerName, ProductGroup

This will count all product codes even if there is no match in the product group table. I haven't tested this, but it should work.

 

[pr2-eugin]

What you need is a GroupBy Query.. Try this..

SELECT SalesData.CustomerName, SalesData.ProdCode, Count(SalesData.Qty) AS CountOfQty 
FROM SalesData INNER JOIN ProdsToGroups ON SalesData.ProdCode = ProdsToGroups.ProdCode
GROUP BY SalesData.CustomerName, SalesData.ProdCode;

See GanzPopp has the answer as well..

 

[paulwilliams]

Hi GanzPopp

Firstly, thanks for such a fast response!

I have tried your query and am recieving the following message:

"You tried to execute a query that does not include the specified expression 'CustomerName' as part of an aggregate function"

I'm about to Google this error but if you can see the problem I'd appriciate your input.

Many thanks once again,
Paul

 

[pr2-eugin]

Paul, Have you tried the suggestion in Post #3 ??

 

[GanzPopp]

Forgot that one in my first attempt. Check the code in my post again and it should work.

Just for your information: the error arises because in general all fields you select which are not used in an aggregate function should be grouped. In your query the 'Customername' field wasn't.

 

[paulwilliams]

Hi All

Again, the speed you guys come up with this stuff is amazing. Both the queries returned results of some kind but didn't quite shows what I was expecting (perhaps I wasn't very clear0. However, I have slightly adapted pr2-eugin's query to the following:

SELECT ProductGroup, COUNT(CustomerName) FROM SalesData 
LEFT JOIN ProdsToGroups ON [SalesData].[ProdCode] = [ProdsToGroups].[ProductCode] 
GROUP BY ProductGroup

This produces a list of Product Groups and the number of times that CustomerName appears. Now lets say a customer buys a '1cm Widget' and then buys a '2cm Widget'; both of these products are part of the 'Widgets' group. With this query, the result shows Widgets=2 but because this is still the same customer I would only like them to be counted once. I have tried to use COUNT DISTINCT but must be using it wrong as I am getting a syntax error.

Thanks guys.
Paul

 

[GanzPopp]

Try this:

SELECT Productgroup, COUNT(UniqCN) AS Total FROM 
(SELECT DISTINCT(CustomerName) AS UniqCN, ProductGroup  FROM SalesData
LEFT JOIN ProdsToGroups ON [SalesData].[ProdCode] = [ProdsToGroups].[ProductCode] 
GROUP BY ProductGroup, CustomerName) 
GROUP BY Productgroup

Since we only want to know which customers bought which product group COUNT is needed on DISTINCT customers (which apparently is only accepted in the first column by Access).

There's probably an easier (no-subquery) way of doing this, but I couldn't think of anything else now...

 

[paulwilliams]

Hi Again,

GanzPopp, I'm not even really interested in which customers bought, only the number of customers that bought. I've taken you're query, displayed it as a pivot and then made the data a COUNT of CustomerName (which is already unique thanks to your query). This has done the buisness. I've included the date field in the query and put this in the column of the pivot to display the customer count each month. The final query looks like this:

SELECT DISTINCT(CustomerName), ProductGroup, SalesDate FROM SalesData 
LEFT JOIN ProdsToGroups ON [SalesData].[ProdCode] = [ProdsToGroups].[ProductCode] 
GROUP BY ProductGroup, CustomerName, SalesDate

I'm going to double-check the figures against a manual count now to ensure I'm getting accurate data but I'm fairly confident.

Thanks GanzPopp and pr2-eugin for your help with this (and the general pointers too). Your help has solved this problem and has given me some extra tools for attacking problems in the future too!

Many thanks

Paul

 

[GanzPopp]

Glad to help. The query is indeed not displaying which customers, but the number of unique customers per product group. Thus exactly what you wanted. My bad.