How to SQL SUM on With Statement?

Question

Query:

;WITH SuperSelect AS (
 SELECT DISTINCT
        DropshipPackinglist.CaseNumber AS 'CASE NO.', 
        DropshipPackinglist.Quantity AS 'QTY'
   FROM DropshipPackinglist 
   JOIN HuaweiDescription ON DropshipPackinglist.ItemNumber = HuaweiDescription.ItemNumber
  WHERE (DropshipPackinglist.BatchCode ='0003721008190AHWA01KG'))
SELECT COUNT(A) as PALLET ,
       [TOTAL VOLUME (MM3)],
       CASE
         WHEN [TOTAL VOLUME (MM3)] = '1090x730x1460' THEN Count(A) * 1.09*0.73*1.46
         WHEN [TOTAL VOLUME (MM3)] = '1090x730x1230' THEN Count(A) * 1.09*0.73*1.23
         WHEN [TOTAL VOLUME (MM3)] = '1090x730x1000' THEN Count(A) * 1.09*0.73*1.00
         WHEN [TOTAL VOLUME (MM3)] = '1090x730x780' THEN Count(A) *  1.09*0.73*0.78
         WHEN [TOTAL VOLUME (MM3)] = '1090x730x570' THEN Count(A) *  1.09*0.73*0.57
         WHEN [TOTAL VOLUME (MM3)] = '1090x730x350' THEN Count(A) *  1.09*0.73*0.35
         ELSE 'Unknown' 
       END as 'WEIGHT'
FROM (SELECT DISTINCT 
                 SUM([QTY]) OVER (partition BY ss.[CASE NO.]) as A,
                 CASE 
                   WHEN sum([QTY]) OVER (partition BY ss.[CASE NO.]) >= 31 
                    AND sum([QTY]) OVER (partition BY ss.[CASE NO.]) <= 36 THEN '1090x730x1460' 
                   WHEN sum([QTY]) OVER (partition BY ss.[CASE NO.]) >= 25 
                    AND sum([QTY]) OVER (partition BY ss.[CASE NO.]) <= 30 THEN '1090x730x1230'
                   WHEN sum([QTY]) OVER (partition BY ss.[CASE NO.]) >= 19 
                    AND sum([QTY]) OVER (partition BY ss.[CASE NO.]) <= 24 THEN '1090x730x1000'
                   WHEN sum([QTY]) OVER (partition BY ss.[CASE NO.]) >= 13 
                    AND sum([QTY]) OVER (partition BY ss.[CASE NO.]) <= 18 THEN '1090x730x780' 
                   WHEN sum([QTY]) OVER (partition BY ss.[CASE NO.]) >= 7 
                    AND sum([QTY]) OVER (partition BY ss.[CASE NO.]) <= 11 THEN '1090x730x570' 
                   WHEN sum([QTY]) OVER (partition BY ss.[CASE NO.]) >= 1 
                    AND sum([QTY]) OVER (partition BY ss.[CASE NO.]) <= 6 THEN '1090x730x350'
                   ELSE 'Unkown' 
                 END AS 'TOTAL VOLUME (MM3)'
            FROM SuperSelect ss) AS CountPallet
GROUP BY [TOTAL VOLUME (MM3)]

I have this Reult:

PALLET  TOTAL VOLUME (MM3)  WEIGHT
--------------------------------------
2       1090x730x1230       1.957422
1       1090x730x1460       1.161722

My Problem is I also Need to Add a Column to Compute the sum of WEiGHT.

Sample Reuslt:

PALLET  TOTAL VOLUME (MM3)  WEIGHT    TOTALWEIGHT
---------------------------------------------------
2       1090x730x1230       1.957422  3.119144
1       1090x730x1460       1.161722  3.119144

Thanks in Regards!

Answer 1

You can use SUM ... OVER()

As the definition of weight is quite lengthy that and your derived table could go into other CTEs.

Something like

;WITH 
SuperSelect AS (SELECT DISTINCT... '0003721008190AHWA01KG'),
CountPallet AS (SELECT... FROM SuperSelect ss),
NewCTE AS (SELECT COUNT(A) ... GROUP BY [TOTAL VOLUME (MM3)])

select PALLET, [TOTAL VOLUME (MM3)], isnull(WEIGHT,'Unknown') AS WEIGHT,
SUM(WEIGHT) OVER() AS TOTALWEIGHT
from NewCTE

You would need to get rid of your else in the case expression to ensure weight was numeric.