Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I currently have a sproc that is used for reporting on a table similar to the one below:

ID      Weight      QTY      OtherData
1         20         12        1234
1         44         4         1234
2         12         1         74747
2         88         7         74747
3         18         8         222222
4         22         9         88888

What I am trying to achieve is that when for example there are multiple instances of ID 1 the Weight & QTY fields need to be added together and merged into one row. The actual schema is far more complex than this but luckily the only unique numbers should be the weight and QTY.

After many hours of googling this I give up and came and asked here. I am not looking for the exact code needed, just an example and a nudge in the right direction. Anticipated result:

ID   Weight QTY    OtherDATA
1    64     16     1234
2    100    8      74747
3    18     8      222222
4    22     9      88888
share|improve this question

closed as unclear what you're asking by Aaron Bertrand Sep 12 '13 at 20:37

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

Hi Alec, thanks for posting here. It looks like the data/example in the original question wasn't sufficient for the real-world problem. However the real-world code is probably too much to expect someone to wade through: can I suggest you put more time and effort into a realistic minimal dataset/code that folk here can work with? SQLFiddle is a pretty useful tool for this… – Jack Douglas Sep 6 '13 at 12:19
I am having some trouble creating an SQL fiddle for SQL server 2008 or even 2012. Think there is an issue on he site. – Alec. Sep 6 '13 at 13:30
I think you're right, it'll probably be fixed soon though by past experience… – Jack Douglas Sep 6 '13 at 13:37
Looks like SQLFiddle is back up and running :) – Jack Douglas Sep 6 '13 at 15:24
@bluefeet has answered your original question. Please post your updated requirements to a new question. Thanks. – Aaron Bertrand Sep 12 '13 at 20:37
up vote 5 down vote accepted

You should be able to get the result by applying the sum() aggregate function to your qty and weight columns and then grouping by the remaining columns in your select statement:

select id,
  Weight = sum(weight),
  qty = sum(qty),
from dbo.yourtable
group by id, otherData;

See Demo

share|improve this answer
Excellent, I had a feeling it would be something simple! Thank you for your answer :) – Alec. Sep 6 '13 at 11:04
This just doubled everything for some reason, extremely confused now! – Alec. Sep 6 '13 at 11:58
@AlecHenderson_v1.00 Well now you have totally changed your question, you should post a new question with those details. Your original question was extremely basic, if you have more details then you should include that in a new question with the exact requirements. Editing a question that has been answered and accepted is not the correct way to proceed. – bluefeet Sep 6 '13 at 12:00
I implemented exactly what you said and it didn't work. Added the Sum() function and grouped the reaming columns in my select statement. From OP " The actual schema is far more complex than this" – Alec. Sep 6 '13 at 12:10
@AlecHenderson_v1.00 Based on your original question and data sample the query will and does work. The problem is now you have expanded with your edit to include a ridiculously long block of code that now needs to be deciphered to determine what should be done. You should have included all of those details in your original question instead of editing after you accepted an answer. – bluefeet Sep 6 '13 at 12:13

Not the answer you're looking for? Browse other questions tagged or ask your own question.