0

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.

9
  • 1
    Try to come up with a minimal example that illustrates your problem. – Laurenz Albe Apr 8 '20 at 14:42
  • @LaurenzAlbe, I've added pictures and more info on the issue – Sue Apr 8 '20 at 14:59
  • You need a sum on your "case" if you want it to be the sum in your resultset – Dominique Boucher Apr 8 '20 at 15:06
  • When I sum the case, it gives this error message Cannot perform an aggregate function on an expression containing an aggregate or a subquery. – Sue Apr 8 '20 at 15:14
  • 1
    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 ;) – Ronaldo Apr 8 '20 at 16:31
0

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.

8
  • Ok testing it out now – Sue Apr 9 '20 at 13:39
  • you could add your "else" in the case, just do not have the "sum" inside the case – Dominique Boucher Apr 9 '20 at 13:41
  • Ok I've added that aspect, but one last question, how do I remove the trailing zeros on the total shipping weight – Sue Apr 9 '20 at 13:48
  • Cast it as either a int or a decimal (with the number of digit you want) like so : sum(convert(numeric(10,0),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" – Dominique Boucher Apr 9 '20 at 13:51
  • Thanks for your help, I figured it out before you answered. But thanks none the less – Sue Apr 9 '20 at 13:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.