时间戳

首页 > 解决方案 > SQL COUNT 销售数量（按部门和位置）

sql - SQL COUNT 销售数量（按部门和位置）

问题描述

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

我认为这些年份需要分开，因为我们希望计数显示某个位置的团队是否在 2017 年或 2018 年（一个或两个）实现了 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/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      | 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         |
+------+----------+----------+----------------+--------------+------------+

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

TOYS SanJose 4

TOYS LasVegas 2

CLOT LasVegas 3

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

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

感谢大家对此的帮助。

标签： sql

解决方案

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

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

推荐阅读