Combining similar columns in a SQL table?

Question

I have a table with a catalogue#, location and quantity. I want to combine all the similar catalogue#'s and have the new row's quantity be the sum of quantity of all the rows with that catalogue#.

For example if i have:

Catalouge#, Location, Quantity
1234, A, 5
1234, B, 10
1234, C, 10
4321, A, 2
4321, B, 3

I want to end up with:

Catalouge#, Location, Quantity
1234, null, 25
4321, null, 5

There are hundred of catalogue numbers so i cant do individual queries for each catalogue number. But that's the only way i can think to do it. Any body have a more generalized way to do it? I prefer to do it in sql but i could do it in c# if necessary.

Answer 1

In C# it is very simple, Please use Hashset and add your all strings into that set. It will automatically not add the duplicate one and finally u will always have a unique string entries int this items. See below sample.

using System.Collections.Generic;

var items = new HashSet<string>();
            items.Add("1234");
            items.Add("1234");

            var count = items.Count; //it will be always one.

Answer 2

Sounds like all you need is grouping:

select
 CatalogueNo, null as Location, sum(Quantity) as Quantity
from ...
where ...
group by CatalogueNo

SQL databases are pretty good at aggregation. Have a look at e.g. https://msdn.microsoft.com/en-us/library/ms173454.aspx