Use mathematical operations when creating table mysql

Basel Ahmed 0
Viewed 23

I need to create a table in mysql workbench and have some column process a mathematical operation, so the problem is that the table processes well until the mathematical operation which starts withSELECT ID, Price, Maintenance_Percentage... I do not know how to overcome this problem here is the table below:

USE test;

CREATE TABLE test1 (

Price double,
High_Specs varchar(80),
Unit_total_Price_with_8_years_Finishing double,
Maintenance_Percentage int
AS 
    (case
when Project="New York" 
     then 10 
when Project="Boston" 
     then 8 
when Project="Florida" 
     then 8 
when Project="Washignton" 
     then 8  
     else 0
     end),
SELECT ID, Price, Maintenance_Percentage, Price * Maintenance_Percentage) /100, as Maintenance_Value, from test1,
Payment_terms varchar(80) );
mysql-workbench mysql
1 Answers

I don't know why you thought a select would be useful here I suggest you create a column for project and another generated column for maintenance_price. Also I would avoid the use of approximate datatypes(double) when dealing with money.

DROP TABLE IF EXISTS T;

CREATE TABLE t (
Price decimal (10,2),
High_Specs varchar(80),
Unit_total_Price_with_8_years_Finishing decimal(10,2),
project varchar(20),
Maintenance_Percentage int
AS 
    (case when Project="New York"  then 10 
             when Project IN("Boston" ,"Florida","Washignton") then 8  
          else 0
     end),
maintenance_value decimal(10,2) as ((Price * Maintenance_Percentage) /100),
Payment_terms varchar(80) );

truncate table t;
insert into t
(Price ,High_Specs ,Unit_total_Price_with_8_years_Finishing ,project)
  values
(10,10,10,'new york');
insert into t
(Price ,High_Specs ,Unit_total_Price_with_8_years_Finishing ,project)
  values
(20,20,20,'FLORIDA');

select * from t;

+-------+------------+-----------------------------------------+----------+------------------------+-------------------+---------------+
| Price | High_Specs | Unit_total_Price_with_8_years_Finishing | project  | Maintenance_Percentage | maintenance_value | Payment_terms |
+-------+------------+-----------------------------------------+----------+------------------------+-------------------+---------------+
| 10.00 | 10         |                                   10.00 | new york |                     10 |              1.00 | NULL          |
| 20.00 | 20         |                                   20.00 | FLORIDA  |                      8 |              1.60 | NULL          |
+-------+------------+-----------------------------------------+----------+------------------------+-------------------+---------------+
2 rows in set (0.016 sec)
P.Salmon0
Related