问题描述

我使用 S**S 创建了一个请求:

I created a request with S**S:

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:

1. 在对该列求和时，您不希望 group by SubTotal.
2. 并且当您尝试对一年内的总数求和时，您不希望 group by OrderDate.相反，您希望 group by 您正在选择的相同计算，例如DATEPART(YYYY, [OrderDate]).

1. You don't want to group by SubTotal when you are summing that column.
2. And you don't want to group by OrderDate when you are trying to sum the totals over a year. Rather you want to group 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