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 ·
Show more comments
richmondo avatar image richmondo commented ·
![alt text][1] [1]: /storage/temp/2979-2015-10-26-142405.png see the attachmnet
0 Likes 0 ·
Show more comments
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 ·
Show more comments
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 ·
Show more comments
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 ·
Show more comments
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.