Link to home
Start Free TrialLog in
Avatar of Leogal
LeogalFlag for United States of America

asked on

Summing columns on duplicate rows in the same table.

My code looks like this:
ase to use
USE DmStagingVD;

    -- table the results write to, this script as it stands runs one company at a time to change the company, 
    -- change the DMFBOMENTITY table name and the where where psco = ?

    DELETE [DMFBOMENTITY3];
    INSERT INTO [DMFBOMENTITY3]


    SELECT   DISTINCT  
    
  -- APPROVAL INFORMATION
                      'YES' AS [BOM_APPROVED],
                      ' ' AS [BOM_APPROVERPARTYNAME],
                      '00005' AS [BOM_APPROVERPARTYNUMBER],
    
	                  '00005' AS [BOM_APPROVERPERSONNELNUMBER]-- BOM INFORMATION & DESCRIPTION
                      ,
                      [PSPMRN] AS [BOM_BOMID],
                      ' ' AS [BOM_CHECKBOM],
                      'Prts' AS [BOM_ITEMGROUPID],

				      ISNULL([EcoResProductTranslation_Name],' ') AS [BOM_NAME],
						
                      ' ' AS [BOM_ROUTE_CONFIGGROUPID],
                      0 AS [BOM_ROUTE_LINENUM],
                      ' ' AS [BOM_ROUTE_NAME],
                      'Variable' AS [BOMCONSUMP],----- ITEM QUANTITY
					   
                       CAST (LTRIM(RTRIM([PSQTY]))AS INT) AS [BOMQTY],

                      1 AS [BOMQTYSERIE]----- BOM TYPE


                      ,
                      CASE 
						WHEN [Phantom] IN ('N', 'P') THEN 'Phantom' 
						WHEN [Phantom] = ' ' THEN 'ITEM' ELSE 'ITEM' 
						END AS [BOMTYPE], 


                      'YES' AS [CALCULATION]---CONFIGURATION INFO
                      ,
                      ' ' AS [CONFIGGROUPID],

                       CASE 
				   WHEN substring([INVENTDIM_CONFIGID],1,15) = PSCMRN THEN [ITEMID] ELSE ' ' END as  [CONFIGID] 


                      ,0 AS [CONSTANT],
                      ' ' AS [DEFINITIONGROUP],
                      0 AS [DENSITY],
                      0 AS [DEPTH],
                      ' ' AS [ENDSCHEDCONSUMP],
                      ' ' AS [EXECUTIONID],
                      ' ' AS [FORMULA]---  FROM DATE
                      ,
                      0 AS [FROMDATE]---- PRODUCT DIMENSIONS: NOT USED AT THIS TIME
                      ,
                      0 AS [HEIGHT],
                      ' ' AS [INVENTBATCHID],
                      ' ' AS [INVENTCOLORID],
                      ' ' AS [INVENTDIMID],
                      ' ' AS [INVENTLOCATIONID],
                      ' ' AS [INVENTSERIALID],
                      'IND' AS [INVENTSITEID],
                      ' ' AS [INVENTSIZEID],
                      ' ' AS [INVENTSTYLEID],
                      0 AS [ISSELECTED],

                      [PSPMRN] AS [ITEMBOMID],--- ITEMID / CHILD PART #,
                      [PSCMRN] AS [ITEMID],
 
                      ' ' AS [ITEMPBAID],
                      ' ' AS [ITEMROUTEID]--- LINE NUMBER AUTOMATED AT THE SERVER LEVEL/ WE DO NOT USE PSOPR
                      ,
                      0 AS [LINENUM],
                      ' ' AS [OPRNUM],
                      ' ' AS [PDSBASEVALUE],
                      0 AS [PDSCWQTY],
                      ' ' AS [PDSINGREDIENTTYPE],--- INHERITIED INFORMATION

                      ' ' AS [PDSINHERITCOPRODUCTBATCHATTRIB],
                      ' ' AS [PDSINHERITCOPRODUCTSHELFLIFE],
                      ' ' AS [PDSINHERITENDITEMBATCHATTRIB],
                      ' ' AS [PDSINHERITENDITEMSHELFLIFE],
                      0 AS [PMFFORMULAPCT],
                      ' ' AS [PMFPCTENABLE],
                      ' ' AS [PMFPLANGROUPID],
                      0 AS [PMFPLANGROUPPRIORITY],
                      ' ' AS [PMFSCALABLE]--- ITEM SEQ #
                      ,
                      CAST (LTRIM(RTRIM(PSSEQ)) AS VARCHAR (20)) AS [POSITION]--- ITEM FINISH
                      ,
                      'FINISH' AS [PRODFLUSHINGPRINCIP],
                      ' ' AS [PROJSETSUBPRODTOCONSUMED],
                      ' ' AS [ROUNDUP],
                      0 AS [ROUNDUPQTY],
                      0 AS [ROWID],
                      0 AS [SCRAPCONST],
                      0 AS [SCRAPVAR]--- TO DATE
                      ,

                      CASE 
						WHEN PSEXMO BETWEEN 1 AND 12 
						THEN substring(CAST (20000000 + [PSEXYR] * 10000 + [PSEXMO] * 100 + [PSEXDA] AS CHAR (10)), 1, 4)
						 + '-' + substring(CAST (20000000 + [PSEXYR] * 10000 + [PSEXMO] * 100 + [PSEXDA] AS CHAR (10)), 5, 2)
						 + '-' + substring(CAST (20000000 + [PSEXYR] * 10000 + [PSEXMO] * 100 + [PSEXDA] AS CHAR (10)), 7, 2) 
						WHEN PSEXYR = 99 THEN '2049-12-31' 
						END AS [TODATE],


                      0 AS [TRANSFERSTATUS]-- UNIT & VENDOR ID
                      ,
					
					 CASE 
						WHEN [BOMUnitId] <> ' '
                                AND [BOMUnitId] <> 'PC' THEN [BOMUnitId] ELSE 'EA' 
                      END AS [UNITID],


                      '  ' AS [VENDID],
                      0 AS [WIDTH],
                      ' ' AS [WMSLOCATIONID],
                      ' ' AS [WMSPALLETID],
                      ' ' AS [WRKCTRCONSUMPTION],
                      0 AS [RECVERSION],
                      0 AS [PARTITION],
                      0 AS [RECID]


    ---  ALL CHAINS BY CHILD ITEM# and Validate that co# is correct, pull on 2014 
    ---- and later years for expiration date (includes 99) 
    ---- and validate that the century PSEXCT is not 20th century (19xx)


    FROM     OPENQUERY ([INDY.ALLEGION.COM], 
	   ' SELECT DISTINCT

				     [PSPMRN]
					,[EcoResProductTranslation_Name]
					,[PSQTY]
					,[Phantom]
					,[PSCMRN]
					,[PSEFCT]
					,[PSEFYR]
					,[PSEFMO]
					,[PSEFDA]
					,[PSSEQ]
					,[PSOPR]
					,[PSEXYR]
					,[PSEXMO]
					,[PSEXDA]
					,[BOMUnitId] 
					,[DisplayProductNumber]
					,[ITEMID]
					,[INVENTDIM_CONFIGID]
		                      	 
			   FROM    [VD_GPL].[DMFPRODUCTENTITY] b
				   inner join [KBM400MFG].[FKPSTRUC] c on b.[DISPLAYPRODUCTNUMBER] = c.[PSCMRN]
 				   where psco = 3 and [PSEXYR] > 14   
 
   ') AS A

Open in new window


the table I am populating looks like this:

Table Name	Column Name	Default Value
DMFBOMENTITY3	BOM_APPROVED	NULL
DMFBOMENTITY3	BOM_APPROVERPARTYNAME	NULL
DMFBOMENTITY3	BOM_APPROVERPARTYNUMBER	NULL
DMFBOMENTITY3	BOM_APPROVERPERSONNELNUMBER	NULL
DMFBOMENTITY3	BOM_BOMID	NULL
DMFBOMENTITY3	BOM_CHECKBOM	NULL
DMFBOMENTITY3	BOM_ITEMGROUPID	NULL
DMFBOMENTITY3	BOM_NAME	NULL
DMFBOMENTITY3	BOM_ROUTE_CONFIGGROUPID	NULL
DMFBOMENTITY3	BOM_ROUTE_LINENUM	NULL
DMFBOMENTITY3	BOM_ROUTE_NAME	NULL
DMFBOMENTITY3	BOMCONSUMP	NULL
DMFBOMENTITY3	BOMQTY	NULL
DMFBOMENTITY3	BOMQTYSERIE	NULL
DMFBOMENTITY3	BOMTYPE	NULL
DMFBOMENTITY3	CALCULATION	NULL
DMFBOMENTITY3	CONFIGGROUPID	NULL
DMFBOMENTITY3	CONFIGID	NULL
DMFBOMENTITY3	CONSTANT	NULL
DMFBOMENTITY3	DEFINITIONGROUP	NULL
DMFBOMENTITY3	DENSITY	NULL
DMFBOMENTITY3	DEPTH	NULL
DMFBOMENTITY3	ENDSCHEDCONSUMP	NULL
DMFBOMENTITY3	EXECUTIONID	NULL
DMFBOMENTITY3	FORMULA	NULL
DMFBOMENTITY3	FROMDATE	NULL
DMFBOMENTITY3	HEIGHT	NULL
DMFBOMENTITY3	INVENTBATCHID	NULL
DMFBOMENTITY3	INVENTCOLORID	NULL
DMFBOMENTITY3	INVENTDIMID	NULL
DMFBOMENTITY3	INVENTLOCATIONID	NULL
DMFBOMENTITY3	INVENTSERIALID	NULL
DMFBOMENTITY3	INVENTSITEID	NULL
DMFBOMENTITY3	INVENTSIZEID	NULL
DMFBOMENTITY3	INVENTSTYLEID	NULL
DMFBOMENTITY3	ISSELECTED	NULL
DMFBOMENTITY3	ITEMBOMID	NULL
DMFBOMENTITY3	ITEMID	NULL
DMFBOMENTITY3	ITEMPBAID	NULL
DMFBOMENTITY3	ITEMROUTEID	NULL
DMFBOMENTITY3	LINENUM	NULL
DMFBOMENTITY3	OPRNUM	NULL
DMFBOMENTITY3	PDSBASEVALUE	NULL
DMFBOMENTITY3	PDSCWQTY	NULL
DMFBOMENTITY3	PDSINGREDIENTTYPE	NULL
DMFBOMENTITY3	PDSINHERITCOPRODUCTBATCHATTRIB	NULL
DMFBOMENTITY3	PDSINHERITCOPRODUCTSHELFLIFE	NULL
DMFBOMENTITY3	PDSINHERITENDITEMBATCHATTRIB	NULL
DMFBOMENTITY3	PDSINHERITENDITEMSHELFLIFE	NULL
DMFBOMENTITY3	PMFFORMULAPCT	NULL
DMFBOMENTITY3	PMFPCTENABLE	NULL
DMFBOMENTITY3	PMFPLANGROUPID	NULL
DMFBOMENTITY3	PMFPLANGROUPPRIORITY	NULL
DMFBOMENTITY3	PMFSCALABLE	NULL
DMFBOMENTITY3	POSITION	NULL
DMFBOMENTITY3	PRODFLUSHINGPRINCIP	NULL
DMFBOMENTITY3	PROJSETSUBPRODTOCONSUMED	NULL
DMFBOMENTITY3	ROUNDUP	NULL
DMFBOMENTITY3	ROUNDUPQTY	NULL
DMFBOMENTITY3	ROWID	NULL
DMFBOMENTITY3	SCRAPCONST	NULL
DMFBOMENTITY3	SCRAPVAR	NULL
DMFBOMENTITY3	TODATE	NULL
DMFBOMENTITY3	TRANSFERSTATUS	NULL
DMFBOMENTITY3	UNITID	NULL
DMFBOMENTITY3	VENDID	NULL
DMFBOMENTITY3	WIDTH	NULL
DMFBOMENTITY3	WMSLOCATIONID	NULL
DMFBOMENTITY3	WMSPALLETID	NULL
DMFBOMENTITY3	WRKCTRCONSUMPTION	NULL
DMFBOMENTITY3	RECVERSION	NULL
DMFBOMENTITY3	PARTITION	NULL
DMFBOMENTITY3	RECID	NULL

Open in new window


Where there are duplicates I need to sum the duplicate row's coulumn BOMQTY, where BOM_BOMID is the same per duplicate row and the ItemID are the same.

I was trying to use this code to remove the duplicates directly after the above query, but it is not totally effective as it leaves some duplicates and of course it does not sum the BOMQTY column

DELETE X  from (
  select *, rn=row_number() over (partition by  [BOM_BOMID] ORDER by [ItemId])
  from   [dbo].[DMFBOMENTITY3]
) x
where rn > 1;

Open in new window


I am trying to incorporate the SUM of the field BOMQTY when the rows have identical BOM_BOMID and ITEMID's within the body of the main script at the top of this question. Unfortunately I am failing miserably. Can someone help me please?  I am using SQL SERVER 2012. Thank you!
Avatar of Valliappan AN
Valliappan AN
Flag of India image

you may try this:

SELECT DISTINCT BOM_BOMID, ITEMID, SUM(BOMQTY) OVER (PARTITION BY  BOM_BOMID, ITEMID ORDER BY BOM_BOMID, ITEMID) 
FROM DMFBOMENTITY3

Open in new window


HTH.
Avatar of John_Vidmar
SELECT	BOM_BOMID
,	ITEMID
,	BOMQTY = SUM(BOMQTY)
FROM	DMFBOMENTITY3
GROUP
BY	BOM_BOMID
,	ITEMID
HAVING	COUNT(*) > 1

Open in new window

Avatar of Leogal

ASKER

@viliappan AN,  When i try to use your code as a subselect with the script like this, i get an error.
 (SELECT DISTINCT BOM_BOMID, ITEMID,
                                SUM(BOMQTY) OVER (PARTITION BY  BOM_BOMID, ITEMID ORDER BY BOM_BOMID, ITEMID)
                        FROM DMFBOMENTITY3) as BOMQTY,
the error: Msg 116, Level 16, State 1, Line 33
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.


@john vidmar,  When i try to use your code as a subselect with the script like this, i get an error.
 
                               (SELECT BOM_BOMID, ITEMID, BOMQTY = SUM(BOMQTY)
                        FROM      DMFBOMENTITY3 GROUP BY      BOM_BOMID,      ITEMID
                         HAVING      COUNT(*) > 1) as BOMQTY,

the error: Msg 116, Level 16, State 1, Line 33
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Avatar of Leogal

ASKER

Trying this..
(select sum(bomqty)
                                          from DMFBOMENTITY3 a
                                          where exists (select 1
                                                              from DMFBOMENTITY3 b
                                                              where b.BOMQTY = a.BOMQTY
                                                              group by b.BOM_BOMID, ITEMID
                                                              having count(distinct b.ITEMID) > 2)) AS BOMQTY,
Hi you just may need to add it as a field and not subquery like this:

SELECT <YOURFIELDSLIST>,
SUM(BOMQTY) OVER (PARTITION BY  BOM_BOMID, ITEMID ORDER BY BOM_BOMID, ITEMID)  FROM <TABLENAME>

HTH
You may use Distinct to avoid duplicate BOM_BOMID, ITEMID in results.
The above solutions are providing a summation for each duplicated BOM_BOMID and ITEMID combination, which may give you multiple records, each record has 3 fields (that is why you cannot collapse into a subquery with a single label).

However, the above solutions can be used as an inline-table (example below), or placed into a with-clause.

Deleting duplicates using an inline-table (note: this example does not need the BOMQTY summation):
DELETE	a
FROM	DMFBOMENTITY3	a
JOIN	(	SELECT	BOM_BOMID
		,	ITEMID
		,	BOMQTY = SUM(BOMQTY)
		FROM	DMFBOMENTITY3
		GROUP
		BY	BOM_BOMID
		,	ITEMID
		HAVING	COUNT(*) > 1
	)	b	ON	a.BOM_BOMID = b.BOM_BOMID
			AND	a.ITEMID = b.ITEMID

Open in new window

John,

>> The above solutions are providing a summation for each duplicated BOM_BOMID and ITEMID combination, which may give you multiple records, each record has 3 fields (that is why you cannot collapse into a subquery with a single label).

Did you read my post https://www.experts-exchange.com/questions/28598252/Summing-columns-on-duplicate-rows-in-the-same-table.html?anchorAnswerId=40555316#a40555316.

Its not to be used as sub query, and its to be included as a field to the fields list in the DISTINCT query to avoid duplicates.

HTH.
Avatar of Leogal

ASKER

My reasoning in using a sub-select is that my results file must have one entry of ITEM and BOM_BOMID in the table for the duplicates and a total of all the quantities for each duplicate records.    @Valliappan AN, you code does remove the duplicates but it appears it deletes all of the duplicates as opposed to keeping a single record with a cumulative total.

When i try my code sub-select i do end up with multiples.
SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I should also like to point out that "select distinct" is the natural enemy of accuracy when summing....

Consider this scenario.

base_table
fldA Qty
abc 1
abc 1
abc 1

select flda, sum(qty) from (
    select distinct flda, qty from base_table
 ) as a

would produce a result of:

fldA Qty
abc 1
instead:

select fldA, sum(Qty) as Qty from base_table group by fldA

would produce:

fldA Qty
abc 3
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
do you still need assistance with this?

if so please confirm what you mean by a duplicate row...

if your duplication test is just related to a couple of the columns , which data values do you wish to maintain in the other columns for that row....

whilst you are considering this ... re-evaluate your open query code and consider what  you are trying to achieve with the distinct clause...

where/why is the duplication occuring in the first place?  
where is it most logical to contain/select the best candidate date from...?

as others have requested  can you supply some test data and expected result so that we can actually see/understand your problem and desired solution.
Avatar of Leogal

ASKER

Sorry for the delay in responding. I was pulled in another direction for two days.
I will be returning to this today.