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) );
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)