問題描述

我使用 SSMS 創(chuàng)建了一個請求:

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] 列中所見，銷售額不會根據(jù) ID 號和名稱相加，但我確實使用了 SUM() 功能在我的選擇中.關(guān)于我的代碼有什么問題的任何想法?

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. 并且當(dāng)您嘗試對一年內(nèi)的總數(shù)求和時，您不希望 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

僅供參考:如果您以類似于以下的格式發(fā)布問題，即最小可重現(xiàn)示例你讓人們更容易、更有可能提供幫助.

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

這篇關(guān)于使用 SUM 函數(shù)根據(jù) Id 和 Name 將我的銷售額加在一起的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！