CODEWITHSUNDEEP
sqlsql-servercreate-tablealter
Meglena Georgieva
Meglena Georgieva

Reputation: 11

SQL counting the values from two columns and showing the result in other column at the same table

I have two columns in one table, looking like this:

CREATE TABLE Invoices
(
  ID_Invoice...
  ....
  UnitPrice decimal (15,2) NOT NULL,
  Discount (15,2) NOT NULL,
)

So I want to add a new column in this table which count the final price - UnitPrice-Discount=TotalCost? Please help.

Upvotes: 1

Views: 691

Answers (2)

Ed Gibbs
Ed Gibbs

Reputation: 26343

If you're using SQL Server, try a calculated column:

CREATE TABLE Invoices
(
  ID_Invoice...
  ....
  UnitPrice decimal (15,2) NOT NULL,
  Discount (15,2) NOT NULL,
  TotalCost AS UnitPrice - Discount
)

If you're using MySQL, your only option is to add a physical column. Don't do that - it denormalizes the table and doesn't bring any benefit. Just query the value on the fly:

SELECT
  ID_Invoice,
  UnitPrice,
  Discount,
  UnitPrice - Discount AS TotalCost

The MySQL query is simple enough, and by not storing the additional column you don't have to worry about the TotalCost, Discount, and UnitPrice columns getting out of sync.

Upvotes: 1

Abhishek Singh
Abhishek Singh

Reputation: 9745

Alter Table Invoices 
Add TotalCost Decimal(15,2)

Will add the column to your table

Now to add values to new column do

Update Invoices
set TotalCost = UnitPrice - Discount

Please let me know if it works

Upvotes: 0

Related Questions