title: Joining multiple tables and applying aggegate giving wrong result
body: <p>I have a business case where when a CountryId is passed to my proc, I need to get all the Regions where the Business is set up in that country, All the Active Sales Employees working in that Region, Total sales done by the current active sales employees in that region.</p>
  <p>My Region table look like below.</p>
  <pre><code>RegionId | Name | CountryId 
      100      A      1          
      101      B      4          
      103      C      1
  </code></pre>
  <p>SalesEmployee Table</p>
  <pre><code>Id | EmployeeId | RegionId
   1       250        100
   2       255        101
   3       289        101
  </code></pre>
  <p>Employee Table</p>
  <pre><code> EmployeeId | Active
      250          1
      255          1
      289          0
      314          1
  </code></pre>
  <p>Sales table</p>
  <pre><code>SaleId | EmployeeId| RegionId |     Sale
    1         100           2         3500
    2         101           4         2000
    3         100           2         1500
  </code></pre>
  <p>My below query is giving me the correct TotalSales value but the TotalUsers count doesn't match.</p>
  <pre><code>Select R.[RegionId], COUNT(SE.[UserId]) AS TotalUsers, SUM(S.[Sales]) AS TotalSales
          FROM dbo.[Region] R
          INNER JOIN [SalesEmployee] SE
          ON R.[RegionId] = SE.[RegionId]
          INNER JOIN dbo.[Employee] E
          ON E.[EmployeeId] = SE.[EmployeeId]
          LEFT JOIN dbo.[Sales] S
          ON S.[EmployeeId] = E.[EmployeeId]
          WHERE R.[CountryId] = 12 AND E.[Active] = 1
          GROUP BY R.[RegionId] 
  </code></pre>
  <p>For Ex RegionId 100 has only 7 Active sales employees currently working but the result gives me 89, in my Employee table there can be many more users but few of them can be inactive and few of them may be working in another department, to make sure that the employee is sales employee the employee needs to be present in SalesEmployee table  and to check if the Employee is Active I need to check in Employee table.</p>
  <p>The problem is 1 single user can have multiple entries on his name in sales table, so when i am joining with Sales table which has multiple entries on a single user then the TotalEmployees count is going  up.</p>