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
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.
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.
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.
![alt text][1] [1]: /storage/temp/2979-2015-10-26-142405.png see the attachmnet
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.
![alt text][1] [1]: /storage/temp/2980-2015-10-26-161405.png Ok this the sales table. What is next to do.
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/
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');
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.
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?
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.
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
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
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.
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
18 People are following this question.
