Remove duplicate entry using SUM() for Qty column

Question

1.80/5 (2 votes)

See more:

Hi,

I want to truncate duplicate rows but Qty should be added

I have a table filled with data,

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

I want the result like,

Item   Qty   MinQty   MaxQty
ABC	25    20        50
XYZ     12    30        40

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

Posted 30-Jul-13 2:50am

chethana M.N

Updated 30-Jul-13 3:24am

Maciej Los

v3

Add a Solution

3 solutions

Solution 1

SQL

Select Item, sum(Qty) as Qty, MinQty, MaxQty
from <your table>
Group by Item, MinQty, MaxQty

Posted 30-Jul-13 3:18am

Tim Carmichael

Updated 30-Jul-13 4:26am

v2

Comments

Maciej Los 30-Jul-13 9:25am

+5

Solution 2

The following statement will work by using a GROUP BY[^] clause:

[Edit]As Maciej pointed out the Qty column needed to be removed from the group by clause.[/Edit]

SQL

SELECT tab.Item, SUM(tab.Qty) as Qty, tab.MinQty, tab.MaxQty
FROM   WhatEverNameYourtTableMayHave as tab
GROUP BY tab.Item, tab.MinQty, tab.MaxQty

MinQty and MaxQty will have to be the same though four each discrete item.

Regards,

— Manfred

Posted 30-Jul-13 3:26am

Manfred Rudolf Bihy

Updated 30-Jul-13 4:23am

v3

Comments

Maciej Los 30-Jul-13 9:52am

Are you shure that Qty shold be in GROUP BY list?

Manfred Rudolf Bihy 30-Jul-13 10:18am

Errhmm no! Sorry I didn't really think about it when I wrote that down.

My bad!

Manfred Rudolf Bihy 30-Jul-13 10:24am

Corrected my solution, thanks for pointing it out!

Cheers!

Maciej Los 30-Jul-13 12:02pm

You're welcome, Manfred ;)
Now, answer deserved for 5!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

chethana M.N · Accepted Answer · 2013-07-30T18:39:00

Solution 3

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

Posted 30-Jul-13 18:39pm

chethana M.N

Updated 30-Jul-13 18:42pm

v2