Reason why the code doesn't sum the values on the match column-CodePudding

I have such an issue. I have a column named 'DC (full)' that sometimes has incosistent data such as 'DC01' and later 'DC01 AUX OPS' which should be regarded as 'DC01', I trimmed it and created a separate column as 'DC'. And later created a 'Match' column that is a concat of Item# and DC.

However, for some reason the code doesn't want to sum values of two columns of 'DC01' and 'DC01 AUX OPS' and I need it to have in one row. Does someone know how it can be fixed?

Code

SELECT INVENTLOCATIONID [DC (full)], substring(INVENTLOCATIONID, 1, charindex(' ', INVENTLOCATIONID   ' ')-1) AS 'DC',
concat(ITEMID, substring(INVENTLOCATIONID, 1, charindex(' ', INVENTLOCATIONID   ' ')-1)) AS 'Match',
          ITEMID [ITEM], 
          INVENTSITEID,
       SUM(PHYSICALINVENT) [INVENTORY OH] 
          
          
FROM   [dbo].[vw_RDSInventSumStagingV2]
WHERE  INVENTSITEID = 01
and ITEMID='9780062377029'
GROUP BY INVENTLOCATIONID, ITEMID, INVENTSITEID
HAVING SUM(PHYSICALINVENT) > 0

Output

CodePudding user response：

The full INVENTLOCATIONID column is used for the GROUP BY clause, and therefore 01 and 01 AUX OPS are different groups. If you need them to be the same group, then indicate that in the GROUP BY:

SELECT substring(INVENTLOCATIONID, 1, charindex(' ', INVENTLOCATIONID   ' ')-1) AS 'DC',
concat(ITEMID, substring(INVENTLOCATIONID, 1, charindex(' ', INVENTLOCATIONID   ' ')-1)) AS 'Match',
          ITEMID [ITEM], 
          INVENTSITEID,
       SUM(PHYSICALINVENT) [INVENTORY OH] 
          
          
FROM   [dbo].[vw_RDSInventSumStagingV2]
WHERE  INVENTSITEID = 01
and ITEMID='9780062377029'
GROUP BY substring(INVENTLOCATIONID, 1, charindex(' ', INVENTLOCATIONID   ' ')-1), ITEMID, INVENTSITEID
HAVING SUM(PHYSICALINVENT) > 0

Of course, now you don't see DC (full) any more, because those records are rolled up with the larger DC group. There could be many different values for the 01 DC (full) group in that same record. You can't have it both ways; you must pick either the group or the individual members of the group.