sql - SQL 根据另一列对一列求和
问题描述
所以我有一张桌子，上面有一堆订单。每个订单都有一个采购订单号（第 5 列）。我的表包含每个订单号的所有行值。例如，订单 3130148 有 5 个已订购的项目，因此它有 5 行。
Company_Code Division_Code Date_Entered Customer_Number Customer_Purchase_Order_Number Item_Number Color_Code Quantity_Ordered Description Ship_Via_Code Order_Value Order_Status Line_Status Price
3 1 00:00.0 ECOM2X 3130183 3102043203L 41420 1 COTTON 4PK BIKINI BRIEF SGRD 0 30 80 12
3 1 00:00.0 ECOM2X 3130183 3102132003S 2221 1 STRETCH 3PK NO SHOW BRIEF SGRD 0 30 80 39
3 1 00:00.0 ECOM2X 3130148 3102012503L 46135 1 COTTON 1PK LONG SLEEVE HENLEY SGRD 58 45 70 9
3 1 00:00.0 ECOM2X 3130148 3102012503L 60120 1 COTTON 1PK LONG SLEEVE HENLEY SGRD 58 45 70 9
3 1 00:00.0 ECOM2X 3130148 3104312001L 40136 1 43100 GRAPHIC COTTON NO-SHOW BREIF SGRD 58 45 80 9.6
3 1 00:00.0 ECOM2X 3130148 3104832001L 46263 1 STRETCH M&M NO SHOW BRIEF SGRD 58 45 70 20
3 1 00:00.0 ECOM2X 3130148 3104832001L 55112 1 STRETCH M&M NO SHOW BRIEF SGRD 58 45 70 20
3 1 48:28.1 ECOM2X 3130143 3104235901S 69615 1 SLIQ MICRO SLIQ BRIEF SGRD 60 45 70 9
3 1 48:28.1 ECOM2X 3130143 3104235901S 90502 1 SLIQ MICRO SLIQ BRIEF SGRD 60 45 70 9
3 1 48:28.1 ECOM2X 3130143 3104234301S 90702 1 SLIQ MICRO SLIQ TRUNK SGRD 60 45 70 9
现在我正在尝试计算总数。像订购的总数量，使用的每个运输代码的总数等。我遇到的问题是所有订单的总价值。我的表有一个订单值列。但是，由于表格是按每个行项目拆分的，我不能只总结整个 Order_Value 列，因为有很多重复项（每行一个）。所以我需要对采购订单号不同的所有订单值求和。
这是我使用的编码行，但它不起作用
Sum(Order_ValueG) FROM (SELECT MAX(Order_Value) AS Order_ValueG FROM T GROUP BY Customer_Purchase_Order_Number) T2 --AS Total_Orders_Value
我在这里找到了这段代码-> SUM(DISTINCT) Based on Other Columns
我尝试多次更改线路，但似乎无法正常工作。我收到关于我的 AS 关键字的错误。
这是完整的代码（我选择了开始语句之后的所有内容并运行它）
ALTER Procedure [dbo].[Z_N_ECOM_ORDER_STATUS_ORDERTOTAL]
@CompanyCode VARCHAR(5),
@DivisionCode VARCHAR(5),
@FromDate DATETIME,
@ToDate DATETIME
AS
DECLARE @LocalCompanyCode VARCHAR(5)
SET @LocalCompanyCode = @CompanyCode
DECLARE @LocalDivisionCode VARCHAR(5)
SET @LocalDivisionCode = @DivisionCode
DECLARE @LocalFromDate DATETIME
SET @LocalFromDate = @FromDate
DECLARE @LocalToDate DATETIME
SET @LocalToDate = @ToDate
BEGIN
SET NOCOUNT ON;
DECLARE @LocalCompanyCode VARCHAR(5)
SET @LocalCompanyCode = '03'
DECLARE @LocalDivisionCode VARCHAR(5)
SET @LocalDivisionCode = '001'
DECLARE @LocalFromDate DATETIME
SET @LocalFromDate = '1/25/2019'
DECLARE @LocalToDate DATETIME
SET @LocalToDate = '1/30/2019'
SELECT DISTINCT
T.Item_Number
,T.Description
,Sum(T.Quantity_Ordered) OVER (PARTITION BY T.Item_Number) as Total_Items_Ordered_Number
,T.color_code
,Sum(T.Quantity_Ordered) OVER (PARTITION BY T.Item_Number, T.Color_Code) as Total_Items_Ordered_NumberColor
,DENSE_RANK() OVER (PARTITION BY T.Ship_Via_Code ORDER BY T.Customer_Purchase_Order_Number ASC)
+ DENSE_RANK() OVER (PARTITION BY T.Ship_Via_Code ORDER BY T.Customer_Purchase_Order_Number DESC)
- 1 AS ShipCode
,DENSE_RANK() OVER (PARTITION BY T.Company_Code ORDER BY T.Customer_Purchase_Order_Number ASC)
+ DENSE_RANK() OVER (PARTITION BY T.Company_Code ORDER BY T.Customer_Purchase_Order_Number DESC)
AS Total_Orders_Count
,Sum(Order_ValueG) FROM (SELECT MAX(Order_Value) AS Order_ValueG FROM T GROUP BY Customer_Purchase_Order_Number) T2 --AS Total_Orders_Value
/*,Sum(T.Price) OVER (PARTITION BY T.Company_Code) as Price
,Sum(T.Discount_Value) OVER (PARTITION BY T.Company_Code) as Discount_Value
,Sum(T.Freight_Charges) OVER (PARTITION BY T.Company_Code) as Freight_Charges
,Sum(T.Tax_Value) OVER (PARTITION BY T.Company_Code) as Tax_Value*/
FROM(
/*DECLARE @LocalCompanyCode VARCHAR(5)
SET @LocalCompanyCode = '03'
DECLARE @LocalDivisionCode VARCHAR(5)
SET @LocalDivisionCode = '001'
DECLARE @LocalFromDate DATETIME
SET @LocalFromDate = '1/25/2019'
DECLARE @LocalToDate DATETIME
SET @LocalToDate = '1/30/2019'*/
SELECT
O.Company_Code
,O.Division_Code
,O.Date_Entered
,O.Customer_Number
,O.Customer_Purchase_Order_Number
,OD.Item_Number
,OD.Color_Code
,OD.Quantity_Ordered
,S.Description
,O.Ship_Via_Code --calculate count of each code
,O.Order_Value -- calculate how much money made from all orders (should match sum of all money made per item)
,O.Order_Status --calculate how many orders were cancelled (30)
,OD.Line_Status --calculate how many items were cancelled (80)
,OD.Price -- calculate how much money made per item
,OD.Discount_Value
,OD.Discount_Percentage
,OD.Tax_Value
,OD.Tax_Percentage
,OD.Freight_Charges
,OD.Freight_Percentage
FROM
[JMNYC-AMTDB].[AMTPLUS].[dbo].Orders O (nolock)
LEFT JOIN
[JMNYC-AMTDB].[AMTPLUS].[dbo].Order_Detail OD (nolock) On O.Company_Code =
OD.Company_Code And O.Division_Code =
OD.Division_Code And O.Control_Number =
OD.Control_Number
LEFT JOIN
[JMNYC-AMTDB].[AMTPLUS].[dbo].Style S (nolock) On OD.Company_Code = S.Company_Code And
OD.Division_Code = S.Division_Code And
OD.Item_Number = S.Item_Number And
OD.Color_Code = S.Color_Code
WHERE
(O.Company_Code = @LocalCompanyCode OR @LocalCompanyCode IS NULL) AND
(O.Division_Code = @LocalDivisionCode OR @LocalDivisionCode IS NULL) AND
o.Customer_Number = 'ecom2x' AND
o.Customer_Purchase_Order_Number not like '%bulk%' AND
o.Date_Entered BETWEEN @LocalFromDate AND DATEADD(dayofyear, 1, @LocalToDate)
--order by date_entered desc
--order by Customer_Purchase_Order_Number desc
) AS T
ORDER BY Total_Items_Ordered_Number desc, Total_Items_Ordered_NumberColor desc
END
我认为该错误与我尝试在子查询中使用表 T 有关。如果这是一个简单的查询，我只会 GROUP BY Customer_Purchase_Order_Number 但由于我选择了多个东西，这不起作用。
解决方案
好吧，你发布的代码太破了，没有用，你还没有分享你的 DDL 或原始数据，所以我要给你一些伪代码的方向。
所以我需要对采购订单号不同的所有订单值求和。
您可以执行以下操作：
SUM(CASE
WHEN ROW_NUMBER() OVER (PARTITION BY PurchaseOrderNumber ORDER BY SomeColumn)=1 THEN OrderValue
ELSE 0
END) AS SumValues