I need to get weight of order, so I need to sum my results This table looks like this
SalesOrderID SalesOrderDetailID SubTotal CompanyName Weight
------------ ------------------ --------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------
71774 110562 880,3484 Good Toys 1061.40
71774 110563 880,3484 Good Toys 988.83
71776 110567 78,81 West Side Mart 317.00
71780 110616 38418,6895 Nearby Cycle Shop 5098.36
71780 110617 38418,6895 Nearby Cycle Shop 24874.88
71780 110618 38418,6895 Nearby Cycle Shop 78053.76
71780 110619 38418,6895 Nearby Cycle Shop 2431.24
71780 110620 38418,6895 Nearby Cycle Shop 12596.19
The query:
SELECT a.SalesOrderID, c.SalesOrderDetailID, a.SubTotal,b.CompanyName,
(SELECT c.OrderQty*d.Weight WHERE c.SalesOrderID=c.SalesOrderID) AS Weight
FROM SalesLT.SalesOrderHeader as a
INNER JOIN SalesLT.Customer AS b
ON a.CustomerID=b.CustomerID
INNER JOIN SalesLT.SalesOrderDetail AS c
ON c.SalesOrderID=a.SalesOrderID
INNER JOIN SalesLT.Product as d
ON d.ProductID=c.ProductID
I've tried to make sum as sum(case when) but this gets me an error Is there any other method? Expected output: 71774 | 880,3484 | Good Toys | 2050,23
2050,23 is a sum of two rows of weight
CodePudding user response：
You can use
WITH TMP_TABLE AS
(
SELECT
a.SalesOrderID,
c.SalesOrderDetailID,
a.SubTotal,
b.CompanyName,
(c.OrderQty * d.Weight) AS Weight
FROM SalesLT.SalesOrderHeader as a
INNER JOIN SalesLT.Customer AS b ON a.CustomerID=b.CustomerID
INNER JOIN SalesLT.SalesOrderDetail AS c ON c.SalesOrderID=a.SalesOrderID
INNER JOIN SalesLT.Product as d ON d.ProductID=c.ProductID
)
SELECT SalesOrderId,
SubTotal,
CompanyName,
SUM(Weight)
FROM TMP_TABLE
GROUP BY SalesOrderId,
SubTotal,
CompanyName
CodePudding user response：
SELECT SalesOrderId,SUM(Weight) SumOfOrderWeights
FROM SalesLT.SalesOrderDetail
GROUP BY SalesOrderId
ORDER BY SalesOrderId
CodePudding user response：
your data
declare @a table(
SalesOrderID INTEGER NOT NULL
,SalesOrderDetailID INTEGER NOT NULL
,SubTotal VARCHAR(60) NOT NULL
,CompanyName VARCHAR(60) NOT NULL
,Weight float NOT NULL
);
INSERT INTO @a
(SalesOrderID,SalesOrderDetailID,SubTotal,CompanyName,Weight) VALUES
(71774,110562,'880,3484','Good Toys',1061.40),
(71774,110563,'880,3484','Good Toys',988.83),
(71776,110567,'78,81','West Side Mart',317.00),
(71780,110616,'38418,6895','Nearby Cycle Shop',5098.36),
(71780,110617,'38418,6895','Nearby Cycle Shop',24874.88),
(71780,110618,'38418,6895','Nearby Cycle Shop',78053.76),
(71780,110619,'38418,6895','Nearby Cycle Shop',2431.24),
(71780,110620,'38418,6895','Nearby Cycle Shop',12596.19);
your query
select SalesOrderID,SubTotal,CompanyName,sum(Weight) Weight from @a
where CompanyName='Good Toys' --removing filter
group by SalesOrderID,SubTotal,CompanyName