mysql add computation results to table column

Question

I have a mysql tables which has column for salary. I was able to compute for the columns 18% and 9% value by using:

SELECT SALARY, SALARY * .09 as 'PS', SALARY * .12 as 'GS' FROM database.table;

1. How would I add an additional column on this table based on the result of the sql statement? What I meant is I wanted to add columns 'PS' and 'GS' to my table.

2. If this is possible on the same table, how would I create another table based on the result of the sql statement (new table containing PS and GS) through sql script/statement.

Answer 1

1) In order to add an additional column to the table you must use the alter command.

ALTER TABLE database.table ADD PS varchar(10); ---- or whatever type of column
ALTER TABLE database.table ADD GS varchar(10); ---- or whatever type of column
UPDATE database.table SET PS = SALARY * .09, GS = SALARY * .12;

2) You must first create the table and then insert into it your previous query.

CREATE TABLE database.secondtable (PS varchar(10), GS varchar(10));
INSERT INTO database.secondtable (PS,GS) VALUES(SELECT SALARY, SALARY * .09 as 'PS', SALARY * .12 as 'GS' FROM database.table);

Answer 2

For question 1, you can simply add the two new fields to the table (I will not get into this here as there is ample documentation on how to add fields). Then run the following query

UPDATE table SET PS = SALARY * .09, GS = SALARY * .12;

FOr the second question you can create a table based on teh result of any query like so:

CREATE TABLE table_name SELECT * FROM orig_table;

Note that you can use any kind of SELECT statement here.

This stuff is really pretty basic SQL. That could easily be found in the MySQL documentation.

Answer 3

If you want the GS to be in same table, you need to take the SQL query values you stated, to a variable and update the table with another query for GS