question

richmondo avatar image
richmondo asked

Updating sql table using select sum

![alt text][1] How to select a value from a table plus a new value that would be inserted to update a table using stored procedure. Find my table bellow. What i want is to select old quantsold + the new Qunatsold to update the same table. Thanks in advance. [1]: /storage/temp/2978-2015-10-26-142405.png
sql-server-2008
13 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

seanlange avatar image seanlange commented ·
You really should post some ddl and sample data instead of an image. Our network prevents me from viewing the picture, or perhaps it is corrupt. Then you need to explain what you want clearly. What column(s) are you trying to update? What is the logic for the new value etc...The more effort you put into your question the more likely it is you will get an answer.
2 Likes 2 ·
richmondo avatar image richmondo commented ·
The table is Inventory_T and the columns are; ID Items Qty Qtysold -------------------------- 1 Fish 100 5 2 Cake 50 10 Now what i want is that qtysold should be updated when ever there is a sales. By selecting the old value plus the new value to update that column.
0 Likes 0 ·
seanlange avatar image seanlange richmondo commented ·
I do not recommend storing values like this because it is easy to get them out of synch. It is generally better to just calculate this as you need the values. The most common method of doing this is either in the procedure for a sale or a trigger on the table.
2 Likes 2 ·
seanlange avatar image seanlange richmondo commented ·
You also still haven't provided details enough so that somebody can really help you here. I don't want to spend my time creating the tables and data so that I can then spend my time writing your code for you. I can help you with the trigger if you put in some effort to make it easier for me.
2 Likes 2 ·
richmondo avatar image richmondo commented ·
![alt text][1] [1]: /storage/temp/2979-2015-10-26-142405.png see the attachmnet
0 Likes 0 ·
seanlange avatar image seanlange richmondo commented ·
Again, I don't feel like creating your tables on my system so I can help you write the code. Your design suffers from a lack of decent normalization. You should have a sales table which tracks the quantity sold. You should also have a purchases table with a quantity. Then you can calculate quantity on hand by taking the sum of purchases - sum of sales.
1 Like 1 ·
richmondo avatar image richmondo commented ·
![alt text][1] [1]: /storage/temp/2980-2015-10-26-161405.png Ok this the sales table. What is next to do.
0 Likes 0 ·
seanlange avatar image seanlange richmondo commented ·
Give me something to work with. Pictures DO NOT translate into anything usable. I can't write a query against a picture. Sure I could create my version of these tables from this but I would have to guess on datatypes. And more importantly, I am not getting paid for this so I have a limited amount of time to offer my services for free. I suspect you would rather have my time working on the problem instead of creating what you can and should have already done. Take a look here for some ideas of what you should be posting instead of pictures. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
2 Likes 2 ·
richmondo avatar image richmondo commented ·
OK So some thing like this? CREATE TABLE [dbo].[Sales_T]( [SalesID] [int] IDENTITY(1,1) NOT NULL, [SalesNum] [int] NULL, [SalesDate] [date] NULL, [Item] [nvarchar](max) NULL, [Qty] [int] NULL, [Rate] [money] NULL, [Amount] [money] NULL, [Memo] [nvarchar](max) NULL, [CustName] [nvarchar](max) NULL, CONSTRAINT [PK_Sales_T] PRIMARY KEY CLUSTERED ( [SalesID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Sales_T] ADD CONSTRAINT [DF_Sales_T_Rate] DEFAULT ((0.00)) FOR [Rate] GO ALTER TABLE [dbo].[Sales_T] ADD CONSTRAINT [DF_Sales_T_Amount] DEFAULT ((0.00)) FOR [Amount] GO INSERT INTO Sales_T VALUES (1,'2015-10-21','SHINE NOSE','5','120000.0000','600000.0000','MD','DORIS SAMUELS'); INSERT INTO Sales_T VALUES (2,'2015-10-21','SNAPA B','5','120000.0000','600000.0000','MD','DORIS SAMUELS');
0 Likes 0 ·
seanlange avatar image seanlange richmondo commented ·
Yeah this is getting somewhere. However, you said you want to update the Inventory table which we don't see here. I assume you want to update the Inventory when a new Sales rows is created? You could do this in the procedure that create the sales row or you could use a trigger on Sales.
0 Likes 0 ·
seanlange avatar image seanlange richmondo commented ·
Yeah something like that. All these answers really should be edits to your question instead of multiple answers. Isn't the original question here how to update the Inventory table? Are you wanting to update the Inventory table whenever there is a new sale? Don't you also need to update the Inventory table when you make a purchase or produce new products? Can you post the ddl for the Inventory table and an explanation of what you want to do?
0 Likes 0 ·
richmondo avatar image richmondo commented ·
CREATE TABLE [dbo].[Inventory_T]( [ItemID] [int] IDENTITY(1,1) NOT NULL, [ItemName] [nvarchar](max) NULL, [QuantOnHand] [int] NULL, [QuantSold] [int] NULL, CONSTRAINT [PK_Inventory_T] PRIMARY KEY CLUSTERED ( [ItemID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Inventory_T] ADD CONSTRAINT [DF_Inventory_T_QuantOnHand] DEFAULT ((0)) FOR [QuantOnHand] GO ALTER TABLE [dbo].[Inventory_T] ADD CONSTRAINT [DF_Inventory_T_QuantSold] DEFAULT ((0)) FOR [QuantSold] GO Insert into Inventory_T values(6,'PUMP','48','0'); INSERT INTO Sales_T VALUES (2,'2015-10-21','SNAPA B','5','120000.0000','600000.0000','MD','DORIS SAMUELS'); UPDATE Inventory_T Set QuantSold = 5+6 Now see, the 5 here is the old value and the 6 is the new inserted value. How can i do this to get 11 as the updated value in the QuantSold column.
0 Likes 0 ·
seanlange avatar image seanlange richmondo commented ·
You really should not keep adding new answers. This stuff should all be in your original question. This structure is horribly denormalized. Your Sales table should have the ItemID, not the text description. Consider what happens when you want to change the name of the item. Also, you really don't need to use varchar(max) for things like Customer Name. Of course, customer name is another example of missing normalization. The customer name should be ONLY in the customer table. In the sales table you should store the CustomerID. I will put together a trigger to demonstrate how you can do this.
1 Like 1 ·
seanlange avatar image
seanlange answered
Here is how you could do this with a trigger. Please note this won't work with your sample insert statement because you don't have a row in Inventory_t for 'SNAPA B'. My previous comments about normalization are still valid. This is in serious need of normalization. You also really don't need varchar(max) for customer name. varchar(100) is probably more than reasonable. The max datatype allows for 2GB of data which is multiple copies of Tolstoy's book "War and Peace". Also for a sales system I would recommend using DateTime as the datatype because that way you can track the busy time slots during the day. At any rate here is an example of how you could do this with a trigger. create trigger Sales_t_Insert on Sales_t for insert as set nocount on; update inv set QuantSold = inv.QuantSold + i.Qty from Inventory_T inv join inserted i on i.Item = inv.ItemName; Notice that if you had posted the ddl and sample data originally this would have been answered several hours ago. If you want some help normalizing your data structures I will be happy to point you in the right direction.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

richmondo avatar image
richmondo answered
CREATE TABLE [dbo].[ItemLine_T]( [ITLID] [int] IDENTITY(1,1) NOT NULL, [ItemID] [int] NULL, [TransType] [varchar](100) NULL, [ItemName] [varchar](100) NULL, [QtySold] [int] NULL, CONSTRAINT [PK_ItemLine_T] PRIMARY KEY CLUSTERED ( [ITLID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] INSERT INTO ItemLine_T VALUES(12,'Sales','ROGGIE','2'); INSERT INTO ItemLine_T VALUES(2,'Sales','POLLOCK','8'); CREATE TABLE [dbo].[Item_T]( [ItemID] [int] IDENTITY(1,1) NOT NULL, [ItemName] [nvarchar](100) NULL, [SubItemOf] [varchar](50) NULL, [ManFacPart] [nvarchar](100) NULL, [ItemDescrip] [nvarchar](100) NULL, [UnitCost] [money] NULL, [SalesPrice] [money] NULL, [QuantOnHand] [int] NULL, [RePoint] [int] NULL, [TotalVal] [money] NULL, CONSTRAINT [PK_Item_T] PRIMARY KEY CLUSTERED ( [ItemID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[Item_T] ADD CONSTRAINT [DF_Item_T_UnitCost] DEFAULT ((0)) FOR [UnitCost] GO ALTER TABLE [dbo].[Item_T] ADD CONSTRAINT [DF_Item_T_SalesPrice] DEFAULT ((0)) FOR [SalesPrice] GO ALTER TABLE [dbo].[Item_T] ADD CONSTRAINT [DF_Item_T_TotalVal] DEFAULT ((0)) FOR [TotalVal] GO INSERT INTO Item_T VALUES('SNAPA B','kb','S-17','big fish from china','235000','240000','35','5','470000'); INSERT INTO Item_T VALUES('SNAPA c','con','B6','rom china','235000','240000','35','5','470000'); CREATE TABLE [dbo].[ReceiveItem_T]( [RecID] [int] IDENTITY(1,1) NOT NULL, [RecItDate] [date] NULL, [RefNo] [nvarchar](100) NULL, [Terms] [nvarchar](100) NULL, [Memo] [nvarchar](100) NULL, [ItemName] [nvarchar](100) NULL, [Descript] [nvarchar](100) NULL, [QuantOnHand] [int] NULL, [Rate] [money] NULL, [Amount] [money] NULL, CONSTRAINT [PK_ReceiveItem_T] PRIMARY KEY CLUSTERED ( [RecID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[ReceiveItem_T] ADD CONSTRAINT [DF_ReceiveItem_T_QuantOnHand] DEFAULT ((0.00)) FOR [QuantOnHand] GO ALTER TABLE [dbo].[ReceiveItem_T] ADD CONSTRAINT [DF_ReceiveItem_T_Rate] DEFAULT ((0.00)) FOR [Rate] GO ALTER TABLE [dbo].[ReceiveItem_T] ADD CONSTRAINT [DF_ReceiveItem_T_Amount] DEFAULT ((0.00)) FOR [Amount] GO INSERT INTO ReceiveItem_T VALUES('2015-10-27','CH12345','2% 10 Net 30','Test entry','KINI','receiving items','5','225000','1125000'); INSERT INTO ReceiveItem_T VALUES('2015-10-28','CH12345','2% 10 Net 30','Test entry','Boat','receiving items','5','225000','1125000'); +--------+--------------------+--------+----------------------+ |Item | Description | Qty | Qty Sold| Qty on Hand| |--------|--------------------|--------|---------|------------| |ROGGIE | For primary scholl | 50 |15 |35 | |POLLOCK | For writing | 20 |10 |10 | |SNAPA B | Fdgfdhdh | 15 |6 |9 | |SNAPA C | Testing | 5 |1 |4 | |--------|--------------------|--------|---------|------------| Example Table Now what I want is to select QuantOnHand from Item_T + QuantOnHand from ReceiveItem_T AS Qty,Sum QtySold from ItemLine_T as [Qty Sold], Sum Qty - [Qty Sold] as [Qty on Hand] Please help
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

richmondo avatar image
richmondo answered
CREATE TABLE [dbo].[ItemLine_T]( [ITLID] [int] IDENTITY(1,1) NOT NULL, [ItemID] [int] NULL, [TransType] [varchar](100) NULL, [ItemName] [varchar](100) NULL, [QtySold] [int] NULL, CONSTRAINT [PK_ItemLine_T] PRIMARY KEY CLUSTERED ( [ITLID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] INSERT INTO ItemLine_T VALUES(12,'Sales','ROGGIE','2'); INSERT INTO ItemLine_T VALUES(2,'Sales','POLLOCK','8'); CREATE TABLE [dbo].[Item_T]( [ItemID] [int] IDENTITY(1,1) NOT NULL, [ItemName] [nvarchar](100) NULL, [SubItemOf] [varchar](50) NULL, [ManFacPart] [nvarchar](100) NULL, [ItemDescrip] [nvarchar](100) NULL, [UnitCost] [money] NULL, [SalesPrice] [money] NULL, [QuantOnHand] [int] NULL, [RePoint] [int] NULL, [TotalVal] [money] NULL, CONSTRAINT [PK_Item_T] PRIMARY KEY CLUSTERED ( [ItemID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[Item_T] ADD CONSTRAINT [DF_Item_T_UnitCost] DEFAULT ((0)) FOR [UnitCost] GO ALTER TABLE [dbo].[Item_T] ADD CONSTRAINT [DF_Item_T_SalesPrice] DEFAULT ((0)) FOR [SalesPrice] GO ALTER TABLE [dbo].[Item_T] ADD CONSTRAINT [DF_Item_T_TotalVal] DEFAULT ((0)) FOR [TotalVal] GO INSERT INTO Item_T VALUES('SNAPA B','kb','S-17','big fish from china','235000','240000','35','5','470000'); INSERT INTO Item_T VALUES('SNAPA c','con','B6','rom china','235000','240000','35','5','470000'); CREATE TABLE [dbo].[ReceiveItem_T]( [RecID] [int] IDENTITY(1,1) NOT NULL, [RecItDate] [date] NULL, [RefNo] [nvarchar](100) NULL, [Terms] [nvarchar](100) NULL, [Memo] [nvarchar](100) NULL, [ItemName] [nvarchar](100) NULL, [Descript] [nvarchar](100) NULL, [QuantOnHand] [int] NULL, [Rate] [money] NULL, [Amount] [money] NULL, CONSTRAINT [PK_ReceiveItem_T] PRIMARY KEY CLUSTERED ( [RecID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[ReceiveItem_T] ADD CONSTRAINT [DF_ReceiveItem_T_QuantOnHand] DEFAULT ((0.00)) FOR [QuantOnHand] GO ALTER TABLE [dbo].[ReceiveItem_T] ADD CONSTRAINT [DF_ReceiveItem_T_Rate] DEFAULT ((0.00)) FOR [Rate] GO ALTER TABLE [dbo].[ReceiveItem_T] ADD CONSTRAINT [DF_ReceiveItem_T_Amount] DEFAULT ((0.00)) FOR [Amount] GO INSERT INTO ReceiveItem_T VALUES('2015-10-27','CH12345','2% 10 Net 30','Test entry','KINI','receiving items','5','225000','1125000'); INSERT INTO ReceiveItem_T VALUES('2015-10-28','CH12345','2% 10 Net 30','Test entry','Boat','receiving items','5','225000','1125000'); +--------+--------------------+--------+----------------------+ |Item | Description | Qty | Qty Sold| Qty on Hand| |--------|--------------------|--------|---------|------------| |ROGGIE | For primary scholl | 50 |15 |35 | |POLLOCK | For writing | 20 |10 |10 | |SNAPA B | Fdgfdhdh | 15 |6 |9 | |SNAPA C | Testing | 5 |1 |4 | |--------|--------------------|--------|---------|------------| Example Table Now what I want is to select QuantOnHand from Item_T + QuantOnHand from ReceiveItem_T AS Qty,Sum QtySold from ItemLine_T as [Qty Sold], Sum Qty - [Qty Sold] as [Qty on Hand] Please help
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

seanlange avatar image seanlange commented ·
PLEASE stop posting new answers over and over. You need to be editing your question. You now have 2 pages of answers to a single question because each "answer" is just another small part of the question. What have you tried for this new query? This is really basic aggregation. You almost wrote the whole query right there.
1 Like 1 ·
richmondo avatar image
richmondo answered
I have solve it by my self. Thank you for your guide lines. CREATE TABLE [dbo].[Inventory_T]( [ItemID] [int] IDENTITY(1,1) NOT NULL, [ItemName] [nvarchar](100) NULL, [QuantOnHand] [int] NULL, CONSTRAINT [PK_Inventory_T] PRIMARY KEY CLUSTERED ( [ItemID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Inventory_T] ADD CONSTRAINT [DF_Inventory_T_QuantOnHand] DEFAULT ((0)) FOR [QuantOnHand] GO INSERT INTO Inventory_T VALUES ('Pen','6'); INSERT INTO Inventory_T VALUES ('Book','10'); CREATE TABLE [dbo].[ItemLine_T]( [ITLID] [int] IDENTITY(1,1) NOT NULL, [TransType] [varchar](100) NULL, [ItemName] [varchar](100) NULL, [QtySold] [int] NULL, CONSTRAINT [PK_ItemLine_T] PRIMARY KEY CLUSTERED ( [ITLID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO INSERT INTO ItemLine_T VALUES ('Pen','2'); INSERT INTO ItemLine_T VALUES ('Book','5'); CREATE VIEW [dbo].[StockView] AS SELECT dbo.Inventory_T.ItemName AS Item, dbo.Inventory_T.QuantOnHand AS Qty, SUM(dbo.ItemLine_T.QtySold) AS [Qty Sold] FROM dbo.Inventory_T INNER JOIN dbo.ItemLine_T ON dbo.Inventory_T.ItemName = dbo.ItemLine_T.ItemName GROUP BY dbo.Inventory_T.ItemName, dbo.Inventory_T.QuantOnHand GO This is the final query SELECT Item, Qty, [Qty Sold], SUM(Qty - [Qty Sold]) AS [Qty on Hand] FROM dbo.stockview GROUP BY Item, [Qty Sold], Qty
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.