Using case and group by in select statement not working

Question

I'm using a case and group by in a select statement and I would like for the case field name (item number) to not be apart of the group by clause statement. I've calculated the invoice weight as well as the shipping weight. This is what I have thus far. The issue is when the calculation is done with the case and group by on the item number, providing multiple rows however, I would like only one row.

    SELECT TOP 10 S.SOPNUMBE, CONCAT(RTRIM(LTRIM(S.CUSTNAME)) ,' ' ,(RTRIM(LTRIM(S.[ADDRESS1]))) ,' ' ,(RTRIM(LTRIM(S.[ADDRESS3]))))AS CUSTNAME,

            S.[CSTPONBR],S.DOCDATE, CAST(round(Sum(QUANTITY),2) as numeric(12,0))TotalOnInvoice,  (CASE 

            WHEN I.ITEMNMBR = 'FG-PD-TJ-PQ-KILOM-FORN'

            THEN  CAST(cast(Sum(QUANTITY * 30 ) as decimal(10,0))as float) 

            WHEN I.ITEMNMBR = 'FG-PD-TJ-PQ-20.0K-FORN'

            THEN  CAST(cast(Sum(QUANTITY * 20 ) as decimal(10,2))as float)                     

            WHEN I.ITEMNMBR = 'FG-PD-TJ-PQ-2.25K-FORN'

            THEN  CAST(cast(Sum(QUANTITY * 2.25 ) as decimal(10,2))as float)

            ELSE CAST(cast(Sum([ITEMSHWT] * Quantity) as decimal(10,0))as float)/100 

            END) AS TotalShippingWeight

            FROM [TWCL].[dbo].[SOP30200] S inner JOIN twcl.dbo.SOP30300 P ON S.SOPNUMBE=P.SOPNUMBE inner join [TWCL].[dbo].[IV00101] I on 

            P.ITEMNMBR= I.ITEMNMBR WHERE CSTPONBR='1343231 ' 

            GROUP BY S.SOPNUMBE, S.CUSTNAME, S.[CSTPONBR], S.DOCDATE, S.[ADDRESS3], S.ADDRESS1,I.ITEMNMBR

This is the outcome I'm receiving now

This is the outcome I would like to receive.

New Query

    SELECT TOP 10 S.SOPNUMBE, CONCAT(RTRIM(LTRIM(S.CUSTNAME)) ,' ' ,(RTRIM(LTRIM(S.[ADDRESS1]))) ,' ' ,(RTRIM(LTRIM(S.[ADDRESS3]))))AS CUSTNAME,

            S.[CSTPONBR],S.DOCDATE, CAST(round(Sum(QUANTITY),2) as numeric(12,0))TotalOnInvoice, SUM (CASE 

            WHEN I.ITEMNMBR = 'FG-PD-TJ-PQ-KILOM-FORN'

            THEN  CAST(cast(Sum(QUANTITY * 30 ) as decimal(10,0))as float) 

            WHEN I.ITEMNMBR = 'FG-PD-TJ-PQ-20.0K-FORN'

            THEN  CAST(cast(Sum(QUANTITY * 20 ) as decimal(10,2))as float)                     

            WHEN I.ITEMNMBR = 'FG-PD-TJ-PQ-2.25K-FORN'

            THEN  CAST(cast(Sum(QUANTITY * 2.25 ) as decimal(10,2))as float)

            ELSE CAST(cast(Sum([ITEMSHWT] * Quantity) as decimal(10,0))as float)/100 

            END) AS TotalShippingWeight

            FROM [TWCL].[dbo].[SOP30200] S inner JOIN twcl.dbo.SOP30300 P ON S.SOPNUMBE=P.SOPNUMBE inner join [TWCL].[dbo].[IV00101] I on 

            P.ITEMNMBR= I.ITEMNMBR WHERE CSTPONBR='1343231 ' 

            GROUP BY S.SOPNUMBE, S.CUSTNAME, S.[CSTPONBR], S.DOCDATE, S.[ADDRESS3], S.ADDRESS1,I.ITEMNMBR

Error message:Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Answer 1

Look at this demo code :

125342', 'Some Fake Name', 1343632, GETDATE(), 3, 'FG-PD-TJ-PQ-20.0K-FORN'),('INV000125342', 'Some Fake Name', 1343632, GETDATE(), 11, 'FG-PD-TJ-PQ-2.25K-FORN'),
('INV000125342', 'Some Fake Name', 1343632, GETDATE(), 2, 'FG-PD-TJ-PQ-2.25K-FORN'),('INV000125342', 'Some Fake Name', 1343632, GETDATE(), 7, 'FG-PD-TJ-PQ-2.25K-FORN'),
('INV000125342', 'Some Fake Name', 1343632, GETDATE(), 2, 'Other'),('INV000125342', 'Some Fake Name', 1343632, GETDATE(), 3, 'Other')


select SOPNUMBE, CUSTNAME, CUSTNBR, DOCDATE,
SUM(QUANTITY) "TotalOnInvoce", 
sum(Case when ITEMNBR='FG-PD-TJ-PQ-KILOM-FORN' then QUANTITY*30
when ITEMNBR='FG-PD-TJ-PQ-20.0K-FORN' then QUANTITY*20
when ITEMNBR='FG-PD-TJ-PQ-2.25K-FORN' then QUANTITY *2.5
end) as "TotalShippingWeight"
from #demo
group by SOPNUMBE, CUSTNAME, CUSTNBR, DOCDATE

drop table #demo

I hope this will help you.