我正在寻找一些帮助,以解决我试图通过使用SQL Server 2014 AdventureWorks2012
实现的结果。
我试图实现的结果是每个销售人员在财政季度的总销售额。
我希望结果是每行 1 个销售人员,其中第 1 季度、第 2 季度、第 3 季度、第 4 季度(2012 年 7 月 - 2013 年 6 月(的总销售额
SELECT DISTINCT
pp.LastName, sp.BusinessEntityID AS SalesPersonID,
SUM(CASE
WHEN MONTH(soh.OrderDate) BETWEEN 7 AND 9 THEN soh.SubTotal END) AS Q1,
SUM(CASE
WHEN MONTH(soh.OrderDate) BETWEEN 10 AND 12 THEN soh.SubTotal END) AS Q2,
SUM(CASE
WHEN MONTH(soh.OrderDate) BETWEEN 1 AND 3 THEN soh.SubTotal END) AS Q3,
SUM(CASE
WHEN MONTH(soh.OrderDate) BETWEEN 4 AND 6 THEN soh.SubTotal END) AS Q4
FROM
Sales.SalesPerson sp
INNER JOIN
Person.Person pp ON sp.BusinessEntityID = pp.BusinessEntityID
INNER JOIN
Sales.SalesOrderHeader soh ON sp.BusinessEntityID = soh.SalesPersonID
WHERE
soh.OrderDate BETWEEN '2012-07-01' AND '2013-06-30'
GROUP BY
sp.BusinessEntityID, pp.LastName, soh.OrderDate;
结果的当前示例;
LastName SalesPersonID Q1 Q2 Q3 Q4
---------------------------------------------------------
Alberts 283 2443.24 NULL NULL NULL
Alberts 283 NULL NULL NULL 32344.342
Alberts 283 NULL 34234 NULL NULL
我期待什么;
LastName SalesPersonID Q1 Q2 Q3 Q4
-----------------------------------------------------------
Alberts 283 2443.24 2324 1142 686858
Jones 287 2443.24 2324 1142 686858
Jimenez 299 2443.24 2324 1142 686858
修复您的GROUP BY
:
GROUP BY sp.BusinessEntityID, pp.LastName;
删除soh.OrderDate
,因为您不希望每个日期都有单独的行。
请注意,SELECT DISTINCT
几乎从不适合GROUP BY
,当然也不适合此查询。