substract the quantity

Question

substract the quantity

rajeevgupta40

Old Hand

Points: 372
More actions
March 30, 2009 at 2:08 am

#219487

i m using sql server 2005 express.I need a query to be able to subtract the total quantity of a product when someone places an order.
i have one table that have the quantity in stock and other table have quantity order.
so how query to show the quantity left in stock ?????

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

It would be easier to answer the question if you posted the DDL for your tables and some sample data as described in the articles in my signature.

Here is a generic solution that may work:

UPDATE dbo.products

SET quantity = P.quantity - O.quantity

FROM

dbo.products P JOIN

dbo.orders O ON

P.product_id = O.product_id

WHERE

O.order_id = @order_id

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

rajeevgupta40 Old Hand Points: 372 More actions · Answer 2

hi thanx for for valuable reply.

actually what i need are explained here :-

i have a product table having column like as:

productID productname suppliername quantity unitprice

1 product1 supplier1 2 22

1 product 2 supplier1 3 34

2 product3 supplier2 2 12

2 product4 supplier1 4 23

where productID and suppliername as composite key

i was in need for total quantity so i did a query like as;

select productID,productname,suppliername,SUM(quantity)as quantity,unitprice from product group by

productID,productname,suppliername,unitprice;

again

i have a another order table have that have the records of placed order which are in product table

structure of order table same as product table like as;-

productID productname suppliername quantity unitprice

1 product1 supplier1 1 22

1 product 2 supplier1 2 34

2 product3 supplier2 1 12

i did the same query for order table for to obtain the total quantity for order placed

and

obtained required result up to here.

next i need to obtain total quantity left in product table after subtracting form order table

like as :

sum (quantity) from product table -sum(quantity)from order table.

so i unable to understand how query to select record with left total quantity in product table group by

productID,productname,suppliername, unitprice.

hope this explaination help u.

thanx again..........

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

This will give you the balance quantity left for a supplier for a product...

SELECTp1.productID, p1.productname, p1.suppliername, p1.quantity - p2.quantity AS quantity

FROMproducts p1

LEFT JOIN

(

SELECT productID, suppliername, SUM( quantity ) AS quantity

FROM orders

GROUP BY productID, suppliername

) p2 ON p2.productID = p1.productID AND p1.suppliername = p2.suppliername

--Ramesh

rajeevgupta40 Old Hand Points: 372 More actions · Answer 4

rajeevgupta40

Old Hand

Points: 372

March 30, 2009 at 1:22 pm

#968606

hiiiii ramesh

thanx for answer

i will check it

rajeevgupta40 Old Hand Points: 372 More actions · Answer 5

hi ramesh

i m sorry because i forgotten adding some information.

i have one addition column purchaseorderIDalong with other column in table product

which is also composite key like other composite key

which is also composite key like other composite key.

and not neccesary that purchaseorderID values and salesorderID is same.

then what would be query for such problem??????

however i used your query

it works but return duplicate rows.

so plz help me.........

thanx

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 6

First of all, the query which I gave you will never return duplicate rows with same product and supplier, as you mentioned they are part of the composite key.

rajeevgupta40 (3/30/2009)
i have a product table having column like as:
productID productname suppliername quantity unitprice
where productID and suppliername as composite key

Secondly, I didn't understand a thing about the PurchaseOrderID and SalesOrderID. Why does it make a difference in the balance quantity?

And also, I need table scripts, data insert scripts & the output required in the format as described here[/url]

--Ramesh

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

rajeevgupta40 (3/30/2009)
sum (quantity) from product table -sum(quantity)from order table.
so i unable to understand how query to select record with left total quantity in product table group by
productID,productname,suppliername, unitprice.
hope this explaination help u.

It's still confusing! Perhaps this will give you some ideas:

SELECT p.productID, p.suppliername, SUM(p.quantity) - SUM(o.quantity)

FROM [product table] p

INNER JOIN [order table] o on o.productID = p.productID AND o.suppliername = p.suppliername

GROUP BY p.productID, p.suppliername

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

rajeevgupta40 Old Hand Points: 372 More actions · Answer 8

hi everyone,

i did a query for obtain sum of total quantity for display in gridview like as;-

select productID,productname,suppliername,SUM(quantity)as quantity,unitprice from product group by

productID,productname,suppliername,unitprice;

next i need to update sum of total quantity "SUM(quantity)" by productID

then what will be query for it??????

plz help me.....

thanx