Adding/subtracting to give result SQL coding

Question

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 22-Mar-17 6:41am

Bpalmereng

Updated 22-Mar-17 7:30am

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2017-03-22T07:17:00

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