So I have a table with a bunch of orders. Each order has a purchase order number (5th column). My table contains all line values of each order number. For example, order 3130148 has 5 items that were ordered so it has 5 lines.
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
Right now I am trying to calculate totals. Like total quantity ordered, total of each shipping code used, etc. The one I'm having a problem with is Total Value of all orders combined. My table has an order value column. However, since the table is split up by each line item, I can't just sum up the whole Order_Value column since there is a lot of duplicates (one for each line). So I need to sum all order values where the Purchase Order Number is different.
This is the line of coded I used but it is not working
Sum(Order_ValueG) FROM (SELECT MAX(Order_Value) AS Order_ValueG FROM T GROUP BY Customer_Purchase_Order_Number) T2 --AS Total_Orders_Value
I found this code here -> SUM(DISTINCT) Based on Other Columns
I tried changing the line many times but I can't seem to get it to work. I get an error about my AS keywords.
Here is the full code (I select everything after the begin statement and run that)
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
I think the error has to do with me trying to use the Table T in my subquery. If this was a simple query, I would just GROUP BY Customer_Purchase_Order_Number but since I am selecting multiple things, that doesn't work.
