使用 SUM 函数根据 Id 和 Name 将我的销售额加在一起
我使用 SSMS 创建了一个请求:
I created a request with SSMS:
SELECT
CONCAT(LastName,' ', FirstName) AS [Nom du vendeur],
SalesPersonID,
DATEPART(YYYY, [OrderDate]) AS [Annee],
FORMAT(SUM(soh.SubTotal), '#,#00.') AS [Ventes]
FROM
Sales.SalesOrderHeader AS soh
INNER JOIN
Person.Person AS pp ON soh.SalesPersonID = pp.BusinessEntityID
WHERE
pp.PersonType = 'SP'
AND soh.OnlineOrderFlag = '0'
AND OrderDate NOT BETWEEN CONVERT(DATETIME, '01/01/2011', 101) AND CONVERT(DATETIME, '12/31/2011', 101)
GROUP BY
SubTotal, OrderDate, SalesPersonID,LastName, FirstName
ORDER BY
[Annee], [Nom du vendeur]
这是我的输出:
正如您在代表销售列的 [ventes]
列中所见,销售额不会根据 ID 号和名称相加,但我确实使用了 SUM()
功能在我的选择中.关于我的代码有什么问题的任何想法?
As you can see in the [ventes]
column which represent the sales column, the sales don't add up depending on the Id number and Name but I do use the SUM()
function in my select. Any ideas as to what is wrong with my code?
推荐答案
问题在于您的group by
:
- 在对该列求和时,您不希望
group by
SubTotal
. - 并且当您尝试对一年内的总数求和时,您不希望
group by
OrderDate
.相反,您希望group by
您正在选择的相同计算,例如DATEPART(YYYY, [OrderDate])
.
- You don't want to
group by
SubTotal
when you are summing that column. - And you don't want to
group by
OrderDate
when you are trying to sum the totals over a year. Rather you want togroup by
the same computation you are selecting e.g.DATEPART(YYYY, [OrderDate])
.
所以你更正的 group by
是:
GROUP BY DATEPART(YYYY, [OrderDate]), SalesPersonID, LastName, FirstName
仅供参考:如果您以类似于以下的格式发布问题,即最小可重现示例你让人们更容易、更有可能提供帮助.
FYI: If you post your questions in a format similar to the following, which is a Minimal Reproducible Example you make it much easier and much more likely for people to assist.
declare @SalesOrderHeader table (id int, SalesPersonID int, OrderDate datetime, SubTotal money, OnlineOrderFlag varchar(1))
declare @Person table (id int, FirstName varchar(64), LastName varchar(64), BusinessEntityID int, PersonType varchar(2))
insert into @Person (id, BusinessEntityID, FirstName, LastName)
select 1, 1, 'Amy', 'Alberts' union all
select 2, 2, 'Pamela', 'Ansman-Wolfe'
insert into @SalesOrderHeader (SalesPersonID, OrderDate, SubTotal)
select 1, '5 nov 2019', 12.34 union all
select 1, '6 nov 2019', 34.56 union all
select 2, '7 nov 2019', 78.90 union all
select 2, '8 nov 2019', 43.21
SELECT
CONCAT(LastName,' ', FirstName) AS [Nom du vendeur]
, SalesPersonID
, DATEPART(YYYY, [OrderDate]) AS [Annee]
, FORMAT(SUM(soh.SubTotal), '#,#00.') AS [Ventes]
FROM @SalesOrderHeader AS soh
INNER JOIN @Person AS pp ON soh.SalesPersonID = pp.BusinessEntityID
--WHERE
-- pp.PersonType = 'SP'
-- AND soh.OnlineOrderFlag = '0'
-- AND OrderDate NOT BETWEEN CONVERT(DATETIME, '01/01/2011', 101) AND CONVERT(DATETIME, '12/31/2011', 101)
-- GROUP BY SubTotal, OrderDate, SalesPersonID,LastName, FirstName
GROUP BY DATEPART(YYYY, [OrderDate]), SalesPersonID, LastName, FirstName
ORDER BY [Annee], [Nom du vendeur]
返回:
Nom du vendeur | SalesPersonID | Annee | Ventes
----------------------------------------------------
Alberts Amy | 1 | 2019 | 47
Ansman-Wolfe Pamela | 2 | 2019 | 122
相关文章