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.
is this possible?
cheers, Dave