SQL/SSRS how to count customer by each product category when they buy in different shop location-CodePudding

I have encounter a problem when I'm trying to count customer by each product category

Please see below sample dataset:

I would like the customer to be count like following:

For the product "Chocolate", even A company & B company bought in different shop location they should only count as 1 customer. Therefore, customer count should only be 9 but with the following code I wrote it comes out 11.

I wrote a query as follow, have to keep all the information becoz i need them to be work in SSRS to do some analysis

Select period
 ,Product
 ,salesshop
 ,customerName
 ,SUM(Qty) AS prodQty
 ,COUNT(DISTINCT customerName) AS CUSTOMERCOUNT
 FROM SHOP_DATA
 group by period
 ,Product
 ,salesshop
 ,customerName
 HAVING salesshop IS NOT NULL

Can anyone help me to fix if possible? Thank you!

CodePudding user response：

If you really want the output you show in your image, you only need to group by product and nothing else

select product, count(distinct customername) as customercount
from shopdata
group by product

CodePudding user response：

SELECT product, COUNT(DISTINCT customername)
FROM shopdata
GROUP BY product

will be enough to have your output

CodePudding user response：

You don't explain in detail the logic you need, but maybe this can help you

first I create a table with the data

declare @t table (product varchar(50), salesshop varchar(50), customername varchar(50))

insert into @t (product, salesshop, customername)
values ('cookies', 'SHOP A', 'A company'), ('cookies', 'SHOP A', 'B company'), ('cookies', 'SHOP A', 'C company'), ('cookies', 'NA_SHOP', 'A company'),
       ('candy', 'SHOP A', 'F company'), ('candy', 'SHOP A', 'G company'),
       ('chocoloat', 'SHOP A', 'H company'), ('chocoloat', 'SHOP A', 'I company'), ('chocoloat', 'SHOP A', 'J company'),
       ('chocoloat', 'SHOP A', 'K company'), ('chocoloat', 'SHOP A', 'L company'), ('chocoloat', 'SHOP A', 'M company'),
       ('chocoloat', 'SHOP A', 'B company'), ('chocoloat', 'NA_SHOP', 'B company'), ('chocoloat', 'NA_SHOP', 'A company'),
       ('chocoloat', 'NA_SHOP', 'F company'), ('chocoloat', 'N SHOP', 'A company')

Now I can run this query on that

select  t2.product, count(t2.customername) as customercount
from   ( 
         select t.product, t.customername
         from  @t t
         group by t.product, t.customername
       ) t2  
group by t2.product

the result will be

product customercount
candy       2
chocoloat   9
cookies     3

Since I don't know the exact logic you need, you need to check the result with your data to see if this is really what you need.

This solution will count the times that a customername appears per product, regardless of the salesshop. So if A company appears 3 times for one product, it counts as only one. This is not only for A company and B company, but for all companies.

I am not sure is this is what you want, so check it with your data.

If this is not what you want, then please edit your question and explain in more detail the logic you need