title: Count Customers based on item master
body: <p>I need you to help me on writing two queries in SQL Server 2008 that shows the following information based on item master:</p>
  
  <ol>
  <li>Brand wise count on customer master plus customer who purchased the brand</li>
  <li>Item Wise count of customer master plus customer who purchased the item</li>
  </ol>
  
  <p>Here the link that shows the table information and the query which I tried.</p>
  
  <p><a href="http://sqlfiddle.com/#!3/fecb0/1" rel="nofollow">Click here to view the table in SQL Fiddle</a></p>
  
  <pre><code>SELECT 
     brandname,
     division,
     route,
     DivisionTotalCustomersCount = MAX(DivisionTotalCustomersCount),
     RouteTotalCustomersCount = MAX(RouteTotalCustomersCount),
     PurchasedCustomersCount = SUM(PurchasedCustomersCount) 
  FROM
     (SELECT 
         i.brandname,
         c.division,
         c.route,
         DivisionTotalCustomersCount = 
            (SELECT COUNT(distinct x.CustomerID) 
             FROM CustomerMaster x 
             WHERE x.division = c.division),
         RouteTotalCustomersCount =
            (SELECT COUNT(distinct x.CustomerID) 
             FROM CustomerMaster x 
             WHERE x.Route = c.route),
         PurchasedCustomersCount = count(distinct C.CustomerID)
                                         FROM CustomerMaster c 
                                         LEFT OUTER JOIN SalesData s on c.CustomerID = s.CustomerID 
                                         right outer join ItemMaster i on s.item = i.itemcode
  
                                         GROUP BY i.brandname, c.division, c.route) A
  
      GROUP BY 
          brandname, division, route
      ORDER BY 1
  </code></pre>
  
  <p>Result Should as below</p>
  
  <p><a href="https://ethercalc.org/mmnzizii2u" rel="nofollow">Excelsheet</a></p>