Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

SQL adding columns in duplicate rows

funtmachine

Posted 30 September 2005 - 01:08 AM

im having heaps of trouble nutting out this sql problem with duplicate rows. I can delete duplicate rows or just select distinct values to avoid them, but here is my problem...

I have a table called StockItems(prodID, WhouseID, Qty). the first column is the id of a product. The second is the warehouse id that it is located in, and the third is the quantity of that product AT THAT WAREHOUSE...

The problem is that say i have the following data;

PRODID | WHOUSEID | QTY

P01 WH01 4
P02 WH02 1
P03 WH01 5
P01 WH03 5

What happens is there can be two records for P01, one for each warehouse.
I need to be able to select it all, with only one record for each prodID, but containing the total QTY, so in this case it would be 9.