title: SQL/SSRS how to count customer by each product category when they buy in different shop location
body: <p>I have encounter a problem when I'm trying to count customer by each product category</p>
  <p>Please see below sample dataset:</p>
  <p><a href="https://i.stack.imgur.com/6aM8Y.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6aM8Y.png" alt="enter image description here" /></a></p>
  <p>I would like the customer to be count like following:</p>
  <p><a href="https://i.stack.imgur.com/tmXBl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/tmXBl.png" alt="enter image description here" /></a></p>
  <p>For the product &quot;Chocolate&quot;, even A company &amp; 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.</p>
  <p>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</p>
  <pre><code>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
  </code></pre>
  <p>Can anyone help me to fix if possible? Thank you!</p>
  <p>problem solved with sub-query lol, thanks everyone</p>