Jump to content

Welcome to Geeks to Go - Register now for FREE

Need help with your computer or device? Want to learn new tech skills? You're in the right place!
Geeks to Go is a friendly community of tech experts who can solve any problem you have. Just create a free account and post your question. Our volunteers will reply quickly and guide you through the steps. Don't let tech troubles stop you. Join Geeks to Go now and get the support you need!

How it Works Create Account
Photo

SQL adding columns in duplicate rows


  • Please log in to reply

#1
funtmachine

funtmachine

    Member

  • Member
  • PipPip
  • 69 posts
Hey guys,

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
  • 0

Advertisements


#2
modernsavage

modernsavage

    Retired Staff

  • Retired Staff
  • 51 posts
Basically you need to perform a count in an SQL statement. A count will only return 1 record for each item which is what you want.

select prodid,count(qty) from stockitem where prodid="P01"

I have not tested the above but have done similar things in the past.
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP