Find those which have same ID and add them up

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Find those which have same ID and add them up

Author

Topic

allan8964
Posting Yak Master

249 Posts

Posted - 2012-11-20 : 12:05:48

Hi there,

Table1 has coulmns of CodeId, TypeId and ModelId, Num1, Num2 ... the Num1, Num2 have data type of int values and the combination of CodeId, TypeId and ModelId can't be the same. In other word, those CodeId, TypeId and ModelId combination must be unique. If any same combination ID found then add up Num1 and Num2 and save the sum as record then old records must be removed. See following sample:

Rec#|CodeId|TypeId|ModelId|Num1|Num2| ...
1 |11 | 13 | 3 | 24 | 18 |
2 |13 | 9 | 2 | 17 | 21 |
3 |11 | 13 | 3 | 29 | 65 |
4 |14 | 13 | 17 | 24 | 18 |

From above table Rec# 1 and 3 have same ID combination. So Rec#1 and 3 must be removed and a new rec with same ID combination and Num1=53, Num2=83 should be added before removal. New rec looks like this:

5 |11 | 13 | 3 | 53 | 83 |

How can I get that? Thanks in advance.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-20 : 12:17:53

Try this in test environment before you apply:

-- Insert into Temp table

Select CodeId,TypeId,ModelId,SUM(Num1)NUM1,SUM,(Num2)NUM2........
into #temp
from Table
Group by CodeId,TypeId,ModelId

-- Delete the entries

Delete P from
(
Select CodeId,TypeId,ModelId,ROW_NUMBER() OVER (PARTITION BY CodeId,TypeId,ModelId Order by CodeId) as Seq
from Table
)P
Where P.Seq > 1

-- Reinsert back

Insert into table

Select CodeId,TypeId,ModelId,NUM1,NUM.....from #temp

allan8964
Posting Yak Master

249 Posts

Posted - 2012-11-20 : 13:21:54

Thanks sodeep. I think the 2nd part of deletion, the P.Seq should be greater than 0 not 1. If P.Seq > 1 it only deletes the duplicated records not all. Later when it comes to 3rd part, write back the #temp table it should write to an empty table.
I am thinking about if it's possible that getting data for only duplicated records in 1st part because the table is huge, about millions records there.
Anyway thanks a lot again.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-20 : 13:36:00

I hope RecNo is and Identity, if not, disregard

CREATE TABLE #temp (RecNo INT IDENTITY (1,1),CodeID Int,TypeID int,ModelID int,Num1 int,Num2 int)

INSERT INTO #temp
VALUES

(11 , 13 , 3 , 24 , 18) ,
(13 , 9 , 2 , 17 , 21) ,
(11 , 13 , 3 , 29 , 65 ),
(14 , 13 , 17 , 24 , 18 )

SELECT *
FROM #temp

SELECT DISTINCT t1.CodeID,t1.TypeID,t1.ModelID
,SUM(Num1) OVER(PARTITION BY t1.CodeID,t1.TypeID,t1.ModelID) AS NewNum1
,SUM(Num1) OVER(PARTITION BY t1.CodeID,t1.TypeID,t1.ModelID) AS NewNum2
,COUNT(*) OVER(PARTITION BY t1.CodeID,t1.TypeID,t1.ModelID) AS NumRows
INTO #temp_Groups
FROM #temp t1

DELETE tgt
FROM #temp tgt
INNER JOIN #temp_Groups src
ON tgt.CodeID = src.codeID AND tgt.typeId = src.typeid AND tgt.ModelID = src.modelID
WHERE src.NumRows > 1

INSERT INTO #temp(CodeID ,TypeID ,ModelID,Num1,Num2)
SELECT CodeID t,TypeID ,ModelID,NewNum1,NewNum2
FROM #temp_Groups
WHERE NumRows>1

SELECT *
FROM #temp

DROP TABLE #temp
DROP TABLE #temp_Groups

Jim

Everyday I learn something that somebody else already knew

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-20 : 13:48:38

[code]-- Insert into Temp table

Select CodeId,TypeId,ModelId,Count(*),SUM(Num1)NUM1,SUM,(Num2)NUM2........
into #temp
from Table
Group by CodeId,TypeId,ModelId
Having Count(*) > 1

-- Delete the entries

Delete t from
table t
inner join #temp p on p.CodeId = t.CodeId and
p.TypeId= t.TypeId and p.ModelId = t.ModelId

-- Reinsert back

Insert into table

Select CodeId,TypeId,ModelId,NUM1,NUM.....from #temp[/code]

allan8964
Posting Yak Master

249 Posts

Posted - 2012-11-20 : 14:47:34

Thanks guys.
jim, the select distinct part adds all the columns, even the ones without duplicated.
sodeep, this time it works perfect!!! That Having Count(*) > 1 clears up the puzzle and deletion hits on only the duplicated combination.
One more question: I use this in a store procedure to insert a new record to table, my question is should I embed the code into the stored procedure at the end or put it into the trigger of table? which one gives the best performance?
Thank you guys again!!!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-20 : 14:53:00

I wouldn't use trigger in this case.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources