按部门和位置计算销售数量

Question

我正在寻找一种在SQL中计算2017年或2018年部门和办公室超过80的销售数量的方法。例如，有多少员工在圣何塞的玩具部门有超过 80 次销售。底部是我想要的输出。我知道有 COUNT，现在 locCount 给了我那个人的销售总数，而我想要的是特定部门超过 80 的 SanJose 销售总数。

我认为年份需要分开，因为我们希望计数显示某个地点的团队是否在 80 年或 2017 年(一个或两个(进行了 2018+ 销售额。所以我正在使用 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 >='01/01/18' and s.dateAdded<='12/31/18' ) and (u.name not like '%TEST%') and hr.Dept in ('TOYS','CLOTHING') and u.email <>' '
GROUP BY hr.location, hr.Dept, u.email, u.Name
HAVING COUNT(*) > 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 >='01/01/17' and s.dateAdded<='12/31/17' ) and (u.name not like '%TEST%') and hr.Dept in ('TOYS','CLOTHING') and u.email <>' '
GROUP BY hr.location, hr.Dept, u.email, u.Name
HAVING COUNT(*) > 80

+------+----------+----------+----------------+--------------+------------+
| Dept | location | loccount | email          | name         | salescount |
+------+----------+----------+----------------+--------------+------------+
| TOYS | SanJose  | 120      | [e-mail]  | Brian Jones  | 120        |
+------+----------+----------+----------------+--------------+------------+
| TOYS | SanJose  | 95       | [e-mail]  | Barry Smith  | 95         |
+------+----------+----------+----------------+--------------+------------+
| TOYS | SanJose  | 98       | [e-mail]  | July Canns   | 98         |
+------+----------+----------+----------------+--------------+------------+
| TOYS | SanJose  | 155      | [e-mail]  | Lamar Pauls  | 155        |
+------+----------+----------+----------------+--------------+------------+
| TOYS | LasVegas | 99       | [e-mail]    | James Lee    | 99         |
+------+----------+----------+----------------+--------------+------------+
| TOYS | LasVegas | 172      | [e-mail] | Jill Miller  | 172        |
+------+----------+----------+----------------+--------------+------------+
| CLOT | LasVegas | 82       | [e-mail]  | Thomas Johns | 82         |
+------+----------+----------+----------------+--------------+------------+
| CLOT | LasVegas | 123      | [e-mail]    | James Lee    | 123        |
+------+----------+----------+----------------+--------------+------------+
| CLOT | LasVegas | 89       | [e-mail]  | Mike Smith   | 89         |
+------+----------+----------+----------------+--------------+------------+

使用上面的输出，结果应该是这样的。

TOYS SanJose 4
TOYS LasVegas 2
CLOT LasVegas 3

如果我从输出中删除 SalesCount，我仍然在 locCount 下得到相同的数字。

我希望我解释得很好。这是我问题的第 1 部分，第 2 部分更复杂，但我认为一次解决一个更容易，我有一种预感，说起来容易做起来难。在这种情况下，我可能只是错误地使用了 COUNT。

感谢大家对此的帮助。

Answer 1

我不确定什么是"团队"。 但是你的问题听起来像是两个级别的聚合。 如果要计算电子邮件，请：

SELECT Dept, Location, COUNT(DISTINCT u.email)
FROM (SELECT hr.Dept, hr.location, u.email, COUNT(*) as salescount
FROM Sales s JOIN
DistrictUser u
ON u.userID = s.districtID JOIN
MyDB.HumanResourcesList hr
ON hr.email = u.email
WHERE s.dateadded >= '2017-01-01' AND
s.dateAdded < '2019-01-01' AND 
u.name not like '%TEST%' AND
hr.Dept in ('TOYS', 'CLOTHING') AND
u.email <> ' '
GROUP BY hr.location, hr.Dept, u.email, u.Name, YEAR(s.dateAdded)
) x
WHERE salesCount > 80
GROUP BY location, Dept