update problem in sql server express

Question

Post reply

update problem in sql server express

rajeevgupta40

Old Hand

Points: 372
More actions
April 20, 2009 at 7:56 am

#208395

hi everyone,
i have a table product and
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

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

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

Gopal Singh SSC Eights! Points: 965 More actions · Answer 1

Hello,

You need to create the table

USE [database name]

GO

/****** Object: Table [dbo].[product] Script Date: 04/20/2009 21:50:11 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[product](

[p_id] [nchar](10) NOT NULL,

[name] [nvarchar](50) NULL,

[supplier name] [nchar](50) NULL,

[quantity] [decimal](10, 4) NULL,

[unit price] [nvarchar](50) NULL

) ON [PRIMARY]

enter some records having with same produt id like '3' and then

run the command which shows the total quantity on the basis of product id

select sum(quantity) from product where p_id='3'

rajeevgupta40 Old Hand Points: 372 More actions · Answer 2

hello,

i have been already created a table "product"

and run a select query.

but i need update query for total sum(quantity) where productID=@productID and suppliername=@suppliername

where productID and suppliername is composite key

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

thanx

Gopal Singh SSC Eights! Points: 965 More actions · Answer 3

update product set[quantity]='500' where p_id='1' // this is the case when you have recods in one table that is procuct. so when you change the quantity then your sum field will automatically reflect the cbanges.

If you need to run query for more then one table the let me know where is the Sum should be stored i.e. procuct or supplier???

Gopal Singh SSC Eights! Points: 965 More actions · Answer 4

update product set[quantity]='25' where p_id='1' and [supplier name]='aa'

i hope you need this query.....:-)

rajeevgupta40 Old Hand Points: 372 More actions · Answer 5

hi,

i want to update total sum of quantity not only quantity.

Lowell SSC Guru Points: 323518 More actions · Answer 6

rajeevgupta40 (4/20/2009)
hi,
i want to update total sum of quantity not only quantity.

you cannot do this.

a query with a group by does not expose data so it can be updated.

you must change the numbers behind the scenes that are used to sum() the quantities and totals.

why don't you explain the reason for updating a total...maybe we can offer a better solution.

most likely you are misunderstanding that you MUST either INSERT a new record or UPDATE an existing to affect the totals.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gopal Singh SSC Eights! Points: 965 More actions · Answer 7

You know that Sum() is taking input as Quantity so anything you need to update Sum means you need to change in quantity. If you need to process on output of Sum() funciton you need to store Sum calue in other fileds then you manipulate it according to you.........

rajeevgupta40 Old Hand Points: 372 More actions · Answer 8

hi

i have been created the product table for the selling of products.

so counted the total sum of like quantity from product table.

next i want to update the total sum of quantity after selling the product.

i think it would help u!

thanx

nandish7 Valued Member Points: 58 More actions · Answer 9

hi

when u say you want to update the table after the products are sold...are you saying the SUM total should reduce?

if so like mentioned earlier in the thread you got to change the numbers behind the scene so that the sum() shows you the correct value