CODEWITHSUNDEEP
mysqlsqlsumcalculated-columns
Thom
Thom

Reputation: 621

MySQL temporary column with calculated field

I have a MySQL table:

enter image description here

Now i want to make a small calculate in the query. I want that there is a temporary column where you see the (product_price + 4).

So when there is a product with the price of 16 (product_price). Then there should be a tempory column e.g. product_newprice = 20

Upvotes: 1

Views: 5974

Answers (4)

Bill Karwin
Bill Karwin

Reputation: 562691

Others have shown how to do an individual SELECT statement to calculate the adjusted value, but this means you have to code this in every query you want to return that value.

If you want to make the column part of the table definition so any query will get the adjusted value, you have these options:

  • Create a VIEW that includes the new column of product_price+4, and only query the VIEW.

  • Add another real column for the adjusted price, and keep it updated with triggers that calculate the new value on INSERT and UPDATE.

  • Use MariaDB, a fork of MySQL, which supports virtual columns, which may be calculated on the fly as expressions against other columns.

Upvotes: 1

Tigran
Tigran

Reputation: 1057

select product_id, product_name, product_price, product_price + 4 as product_price2 from YourTable.

Upvotes: 1

spencer7593
spencer7593

Reputation: 108470

Query would look like this:

SELECT p.product_id
     , p.product_name
     , p.product_price
     , p.product_price + 4.00 AS product_newprice
  FROM mytable p

Upvotes: 1

Mihai
Mihai

Reputation: 26784

SELECT product_id,product_name,product_price,(product_price +4) product_newprice  FROM table

Upvotes: 1

Related Questions