title: SQL COUNT number of sales by Department and Location
body: <p>I am looking for a way in SQL to count the number of sales that were over 80 by Department and by Office in 2017 OR 2018. For example, how many employees had over 80 sales in the TOY department in SanJose. At the bottom is my desired output. I know there is COUNT, right now locCount is giving me the total number of sales by that person when what I want is the total number of SanJose sales in a specific department that were over 80.</p>
  
  <p>I think The years need to be kept separated as we want the count to show if a team in a certain location made 80+ sales in either 2017 OR 2018 (one or both). So I am using UNION to accomplish this. </p>
  
  <pre><code>SELECT hr.Dept, hr.location, COUNT(1)locCount, u.email, u.Name, Count(*)SalesCount, ‘Frontline’ AS SalesType FROM Sales s
  join DistrictUser u
  ON u.userID = s.districtID
  Join MyDB.HumanResourcesList hr
  ON (hr.email = u.email)
   WHERE (s.dateadded &gt;='01/01/18' and s.dateAdded&lt;='12/31/18' ) and (u.name not like '%TEST%') and hr.Dept in ('TOYS','CLOTHING') and u.email &lt;&gt;' '
  GROUP BY hr.location, hr.Dept, u.email, u.Name
  HAVING COUNT(*) &gt; 80
  
  UNION
  
  SELECT hr.Dept, hr.location, COUNT(1)locCount, u.email, u.Name, Count(*)SalesCount, ‘Frontline’ AS SalesType FROM Sales s
  join DistrictUser u
  ON u.userID = s.districtID
  Join MyDB.HumanResourcesList hr
  ON (hr.email = u.email)
   WHERE (s.dateadded &gt;='01/01/17' and s.dateAdded&lt;='12/31/17' ) and (u.name not like '%TEST%') and hr.Dept in ('TOYS','CLOTHING') and u.email &lt;&gt;' '
  GROUP BY hr.location, hr.Dept, u.email, u.Name
  HAVING COUNT(*) &gt; 80
  
  
  +------+----------+----------+----------------+--------------+------------+
  | Dept | location | loccount | email          | name         | salescount |
  +------+----------+----------+----------------+--------------+------------+
  | TOYS | SanJose  | 120      | bjones@ks.com  | Brian Jones  | 120        |
  +------+----------+----------+----------------+--------------+------------+
  | TOYS | SanJose  | 95       | bsmith@ks.com  | Barry Smith  | 95         |
  +------+----------+----------+----------------+--------------+------------+
  | TOYS | SanJose  | 98       | jcanns@ks.com  | July Canns   | 98         |
  +------+----------+----------+----------------+--------------+------------+
  | TOYS | SanJose  | 155      | lpauls@ks.com  | Lamar Pauls  | 155        |
  +------+----------+----------+----------------+--------------+------------+
  | TOYS | LasVegas | 99       | jlee@ks.com    | James Lee    | 99         |
  +------+----------+----------+----------------+--------------+------------+
  | TOYS | LasVegas | 172      | jmiller@ks.com | Jill Miller  | 172        |
  +------+----------+----------+----------------+--------------+------------+
  | CLOT | LasVegas | 82       | tjohns@ks.com  | Thomas Johns | 82         |
  +------+----------+----------+----------------+--------------+------------+
  | CLOT | LasVegas | 123      | jlee@ks.com    | James Lee    | 123        |
  +------+----------+----------+----------------+--------------+------------+
  | CLOT | LasVegas | 89       | msmith@ks.com  | Mike Smith   | 89         |
  +------+----------+----------+----------------+--------------+------------+
  </code></pre>
  
  <p>The results should be something like this using the above output.</p>
  
  <pre><code>TOYS SanJose 4
  
  TOYS LasVegas 2
  
  CLOT LasVegas 3
  </code></pre>
  
  <p>If I remove SalesCount from the output, I still get the same numbers under locCount.</p>
  
  <p>I hope I am explaining well. This is part 1 of my question, part 2 is more complicated, but I thought it easier to tackle one at a time and I have a hunch this is easier said than done. I may just be using COUNT wrong in this context.</p>
  
  <p>Thanks all for your help with this.</p>