Click here to Skip to main content
15,074,394 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
See more:
Can someone help explain to me how to take int values in SQL from rows and produce a Total that updates a field without me having to do the math manually. Or an example to look at so I can better understand MYSQL and how it would work


CREATE TABLE [dbo].[Table]
(
	[Id] INT NOT NULL PRIMARY KEY, 
    [Type] VARCHAR(50) NULL, 
    [MUTCD] VARCHAR(50) NULL, 
    [Sign] VARCHAR(50) NULL, 
    [Size] VARCHAR(50) NULL, 
    [Hi_Int] VARCHAR(50) NULL, 
    [Beginning_Inventory] INT NULL, 
    [Purchased_Inventory] INT NULL, 
    [Used_Inventory] INT NULL, 
    [Ending_Inventory] INT NULL
)


The Section below is the 4 fields I need to add and subtract from.
I need Purchased_Inventory to Add to the total, Used_Inventory to Subtract from the total. Then the result to be posted to the Ending_Inventory cell.
[Beginning_Inventory] INT NULL, 
    [Purchased_Inventory] INT NULL, 
    [Used_Inventory] INT NULL, 
    [Ending_Inventory] INT NULL


Beggining Inventory= 30
Purchased Inventory= 5
Used_Inventory= 15
TOTAL Inventory= result(30+5-15=20)

I basically need my SQL database code to do this..



If this is still unclear please let me know and I will try and rephrase it better.

What I have tried:

I have looked at stuff on how to do it but I'm just not sure. This website has better resources and knowledge plus the answers are easier to understand. Thanks for your time.
Posted
Updated 22-Mar-17 7:30am
v2
Comments
[no name] 22-Mar-17 13:13pm
   
"30+5-15=25", forget the inventory, can you get a job working at my bank?

"still unclear", through all of your postings and deleting of postings, it has been unclear *What you have tried*.
Bpalmereng 22-Mar-17 13:29pm
   
Yea I made a mistake I'm aggravated because Instead of questioning and having me rephrase the post is deleted. Everything I see people get Totals from columns. Where as I'm trying to produce a total for each individual row.


select Id, Beginning_Inventory, Purchased_Inventory, Used_Inventory,
Beginning_Inventory + Purchased_Inventory - Used_Inventory as Total_Inventory
FROM myTable;
This I get. What I Don't get is how to have that Automatically Insert to the last Columns field.

1 solution

Look at what you have done manually...
You've taken the value of Beginning_Inventory and added the value of Purchased_Inventory then subtracted the value of Used_Inventory

I wonder what would happen if you did that in a SELECT statement...
SQL
select Id, Beginning_Inventory, Purchased_Inventory, Used_Inventory, 
Beginning_Inventory + Purchased_Inventory - Used_Inventory as Total_Inventory
FROM myTable;


There are some examples of how MySQL works on this link - MySQL Tutorial - MySQL By Examples for Beginners[^]

[EDIT]
If you want to update the last column then the syntax is
SQL
UPDATE myTable SET Ending_Inventory = Beginning_Inventory + Purchased_Inventory - Used_Inventory;
Note that this is not good practice - just calculate the final inventory when you need it rather than storing extraneous information
   
v2
Comments
Bpalmereng 22-Mar-17 13:24pm
   
Right but I'm not trying to get the total of those columns due to me having multiple rows. Im trying to get a total for a row from multiple columns and then have that post to the last column in the same row. "BTW I also might be trying to do something thats not possible I just don't want to have to build a table for every single Item that needs its inventory done.

ID | Beggining Inventory | Purchased | Used | Total
1 30 10 15 25
1 30 10 15 25
1 30 10 15 25
1 30 10 15 25
1 30 10 15 25
1 30 10 15 25
CHill60 22-Mar-17 13:38pm
   
I've updated my solution.
Bpalmereng 22-Mar-17 13:42pm
   
Thank you so much that answered my question and the advise.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900