Remove duplicate entry in sql query

Question

Hi,

I want to truncate duplicate rows but Qty should be added

I have a table filled with data,

Item Qty MinQty MaxQty
ABC10 20 50
XYZ 12 30 40
ABC 15 20 50

I want the result like,
Item Qty MinQty MaxQty
ABC25 20 50
XYZ 12 30 40

Kindly help me to write the query for the same...

Iftikar Hussain · Answer

Hi, Try like this without any temp table select Item,SUM(Qty),MinQty,MaxQty from Table1 group by Item,MinQty,MaxQty Regards, Iftikar

Iftikar Hussain · Answer

this query will not repeat if Item,MinQty and MaxQty are same Regards, Iftikar

Rosi Sreenivasa Reddy · Answer

Hai Murali, your answer also smart

Rosi Sreenivasa Reddy · Answer

Hai iftikar Husssain, your answer is smart select Item,SUM(Qty),MinQty,MaxQty from Table1 group by Item,MinQty,MaxQty but i need only distinct columns in that table Ex:repeation is not allowed(ABC)

Iftikar Hussain · Answer

you welcome Regards, Iftikar

chethana mn · Answer

Thanks for ur kind reply, ur query working fine for me.. i was looking for dat result. u guys saved my time.

Muralidharan Deenathayalan · Answer

I included the temp table, he may need to insert back the records into the original table, otherwise, SELECT name, sum (qty) as qty, sum (minqty) as minqty, sum (maxqty) as maxqty FROM table_1 GROUP BY name is sufficient for you.

Muralidharan Deenathayalan · Answer

SELECT name, sum (qty) as qty, sum (minqty) as minqty, sum (maxqty) as maxqty, count (*) as cnt into #temp1 FROM table_1 GROUP BY name HAVING count (*) > 1 union SELECT name, sum (qty) as qty, sum (minqty) as minqty, sum (maxqty) as maxqty, count (*) as cnt FROM table_1 GROUP BY name HAVING count (*) = 1 select name,qty,minqty,maxqty from #temp1 First i'm taking all the duplicated rows union with non-duplicated rows then inserting into #temp1 table. Now the #temp1 table has all the combined (non-duplicate records). input table name qty minqty maxqty abc 25 40 100 xyz 12 30 40 Output table name qty minqty maxqty abc 25 40 100 xyz 12 30 40

Remove duplicate entry in sql query

Answers (8)