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...
Loading
Iftikar HussainPosted Jul 30, 2013, 12:07 PM
Try like this without any temp table
select Item,SUM(Qty),MinQty,MaxQty from Table1 group by Item,MinQty,MaxQty
Regards,
Iftikar
Iftikar HussainPosted Jul 31, 2013, 2:14 AM
Regards,
Iftikar
Rosi Sreenivasa ReddyPosted Jul 31, 2013, 2:12 AM
your answer also smart
Rosi Sreenivasa ReddyPosted Jul 31, 2013, 2:11 AM
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 HussainPosted Jul 31, 2013, 12:44 AM
Regards,
Iftikar
chethana mnPosted Jul 31, 2013, 12:40 AM
Posted Jul 30, 2013, 12:47 PM
SELECT name, sum(qty) as qty,sum(minqty) as minqty, sum(maxqty) as maxqty
FROM table_1
GROUP BY name
is sufficient for you.
Posted Jul 30, 2013, 11:25 AM
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