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

enter image description here

This is the outcome I would like to receive.

enter image description here

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.

• Laurenz Albe: Try to come up with a minimal example that illustrates your problem.
• Sue: @LaurenzAlbe, I've added pictures and more info on the issue
• Dominique Boucher: You need a sum on your "case" if you want it to be the sum in your resultset
• Sue: When I sum the case, it gives this error message Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
• Ronaldo: Welcome to Database Administrators. Please, check How to create a Minimal, Complete, and Verifiable Example for database-related questions to better understand what Laurenz Albe asked for. That shall help us to better help you ;)

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.