How to Nested Case in Select SQL?

Question

I have this select Case SQL statement which compute the totalvolume of a given quantity.

---

SELECT
DropshipPackinglist.CaseNumber as 'CASE NO.',  
DropshipPackinglist.ItemNumber as 'BOM NO.', 
DropshipPackinglist.Quantity as 'QTY',
                      CASE 
                      WHEN DropshipPackinglist.Quantity >=31 and DropshipPackinglist.Quantity <= 36 then '1090x730x1460'
                      WHEN DropshipPackinglist.Quantity >=25 and DropshipPackinglist.Quantity <= 30  then '1090x730x1230'
                      WHEN DropshipPackinglist.Quantity >=19 and DropshipPackinglist.Quantity <= 24  then '1090x730x1000'
                      WHEN DropshipPackinglist.Quantity >=13 and DropshipPackinglist.Quantity <= 18  then '1090x720x790'
                      WHEN DropshipPackinglist.Quantity >=7 and DropshipPackinglist.Quantity <= 17  then '1090x720x570'
                      WHEN DropshipPackinglist.Quantity >=1 and DropshipPackinglist.Quantity <= 6  then '1090x720x350'

                      ELSE 'Unkown' 
                      end
                      as 'TOTAL VOLUME (MM3)'                                      

FROM         DropshipPackinglist INNER JOIN
                      HuaweiDescription ON DropshipPackinglist.ItemNumber = HuaweiDescription.ItemNumber

WHERE     (DropshipPackinglist.BatchCode LIKE '%0005041007100AHWA11HG')

-------------------------------------------------------------------------------------------
Result:

CaseNumber ItemNumber      Quantity  TotalVolume
1     52411573  5   1090x720x350
1     52411576  20  1090x730x1000
2     52411576  36  1090x730x1460

-------------------------------------------------------------------------------------------

Now is, i want to group casenumber and result with only one totalvolume.

And the result will be this one.

CaseNumber ItemNumber      Quantity  TotalVolume
1     52411573  5   1090x730x1230  -- sum(casenumber 1)=25
1     52411576  20  1090x730x1230  --
2     52411576  36  1090x730x1460

How to solve this one..thanks in regards.

Answer 1

If you need just one row per caseNumber then use

SELECT CaseNumber, Quantity, SUM(ItemNumber) TotalVolume 
FROM (...YourOriginalQuery...)
GROUP BY CaseNumber, Quantity

If you need all rows but want also report Total per case number then use the following query:

SELECT CaseNumber, ItemNumber, Quantity,
                 SUM(ItemNumber) OVER(PARTITION BY CaseNumber) TotalVolume
    FROM   (SELECT DropshipPackinglist.CaseNumber, DropshipPackinglist.ItemNumber,
                                    DropshipPackinglist.Quantity,
                                    CASE
                                         WHEN DropshipPackinglist.Quantity >= 31
                                                    AND DropshipPackinglist.Quantity <= 36 THEN
                                            '1090x730x1460'
                                         WHEN DropshipPackinglist.Quantity >= 25
                                                    AND DropshipPackinglist.Quantity <= 30 THEN
                                            '1090x730x1230'
                                         WHEN DropshipPackinglist.Quantity >= 19
                                                    AND DropshipPackinglist.Quantity <= 24 THEN
                                            '1090x730x1000'
                                         WHEN DropshipPackinglist.Quantity >= 13
                                                    AND DropshipPackinglist.Quantity <= 18 THEN
                                            '1090x720x790'
                                         WHEN DropshipPackinglist.Quantity >= 7
                                                    AND DropshipPackinglist.Quantity <= 17 THEN
                                            '1090x720x570'
                                         WHEN DropshipPackinglist.Quantity >= 1
                                                    AND DropshipPackinglist.Quantity <= 6 THEN
                                            '1090x720x350'
                                         ELSE
                                            'Unkown'
                                    END AS 'TOTAL VOLUME (MM3)'
                     FROM   DropshipPackinglist
                     INNER  JOIN HuaweiDescription
                     ON     DropshipPackinglist.ItemNumber = HuaweiDescription.ItemNumber
                     WHERE  (DropshipPackinglist.BatchCode LIKE '%0005041007100AHWA11HG'))

Answer 2

;with SuperSelect as 
(
 SELECT  dpl.CaseNumber as 'CASE NO.'
     ,dpl.ItemNumber as 'BOM NO.'
     ,dpl.Quantity as 'QTY'
     ,CASE WHEN dpl.Quantity >= 31 and dpl.Quantity <= 36 then '1090x730x1460'
     WHEN dpl.Quantity >= 25 and dpl.Quantity <= 30 then '1090x730x1230'
     WHEN dpl.Quantity >= 19 and dpl.Quantity <= 24 then '1090x730x1000'
     WHEN dpl.Quantity >= 13 and dpl.Quantity <= 18 then '1090x720x790'
     WHEN dpl.Quantity >= 7 and dpl.Quantity <= 17 then '1090x720x570'
     WHEN dpl.Quantity >= 1 and dpl.Quantity <= 6 then '1090x720x350'
     ELSE 'Unkown'
   end as 'TOTAL VOLUME (MM3)'
 FROM    DropshipPackinglist dpl
 INNER JOIN HuaweiDescription hd ON dpl.ItemNumber = hd.ItemNumber
 WHERE   (dpl.BatchCode LIKE '%0005041007100AHWA11HG')
)
select *, sum([QTY]) over (partition by ss.[CASE NO.]) [TotalVolume]
from SuperSelect ss