Add Value of same item number

Question

Add Value of same item number

Dee Dee-422077

Ten Centuries

Points: 1082
More actions
August 26, 2015 at 2:57 pm

#311113

hi ,
My data look like this:
ITEMID DESC QTY PRICE
134 ITEM1 4 $200
134 ITEM1 1 $200
134 ITEM1 1 $200
Can you please advice me how do I write SQL stmt to add the quantity if the itemid is the same?
The result should be:
ITEMID DESC QTY PRICE
134 ITEM1 6 $200
Thanks,
Dee

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Andrew P SSCarpal Tunnel Points: 4649 More actions · Answer 1

You'll need to either group by all of the items you don't want summed (added together) or run a separate subquery to get the summed values as required, which would have worse performance.

Try this:

SELECT ITEMS.ITEMID

, ITEMS.DESC

, SUM(QTY) AS QTY

, ITEMS.PRICE

FROM ITEMS

GROUP BY ITEMS.ITEMID

, ITEMS.DESC

, ITEMS.PRICE

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 2

Dee Dee-422077 (8/26/2015)
hi ,
My data look like this:
ITEMID DESC QTY PRICE
134 ITEM1 4 $200
134 ITEM1 1 $200
134 ITEM1 1 $200
Can you please advice me how do I write SQL stmt to add the quantity if the itemid is the same?
The result should be:
ITEMID DESC QTY PRICE
134 ITEM1 6 $200
Thanks,
Dee

Gosh... please don't take this as being mean but this is very, very basic. If you're going to have to write SQL code and this isn't for some course, I'd strongly recommend you get some training or at least read a book or two on the subject. If you're going to be writing SQL a lot, then I recommend you spend <$60USD and buy the Developer's Edition of SQL Server, download "Books Online", and visit a site like W3Schools.com and start training yourself.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)