Leogal
asked on
Summing columns on duplicate rows in the same table.
My code looks like this:
the table I am populating looks like this:
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
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!
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
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
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;
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!
SELECT BOM_BOMID
, ITEMID
, BOMQTY = SUM(BOMQTY)
FROM DMFBOMENTITY3
GROUP
BY BOM_BOMID
, ITEMID
HAVING COUNT(*) > 1
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.
(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.
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,
(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
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):
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
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.
>> 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.
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.
When i try my code sub-select i do end up with multiples.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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 distinct flda, qty from base_table
) as a
would produce a result of:
fldA Qty
abc 1
select fldA, sum(Qty) as Qty from base_table group by fldA
would produce:
fldA Qty
abc 3
would produce:
fldA Qty
abc 3
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Sorry for the delay in responding. I was pulled in another direction for two days.
I will be returning to this today.
I will be returning to this today.
Open in new window
HTH.