Add a temporary column in MySQL where the values depend on another column?

You can use CASE statement for this and set conditions to get result in the temporary column.

Let us first create a table −

mysql> create table DemoTable
   (
   EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   EmployeeName varchar(20),
   EmployeeSalary int,
   EmployeeExperience int
   );
Query OK, 0 rows affected (0.64 sec)

Following is the query to insert some records in the table using insert command −

mysql> insert into DemoTable(EmployeeName,EmployeeSalary,EmployeeExperience) values('Larry',4500,5);
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable(EmployeeName,EmployeeSalary,EmployeeExperience) values('Mike',130000,8);
Query OK, 1 row affected (0.21 sec)
mysql> insert into DemoTable(EmployeeName,EmployeeSalary,EmployeeExperience) values('Sam',11000,5);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable(EmployeeName,EmployeeSalary,EmployeeExperience) values('Carol',140000,8) ;
Query OK, 1 row affected (0.18 sec)

Following is the query to display records from the table using select command −

mysql> select *from DemoTable;

This will produce the following output −

+------------+--------------+----------------+--------------------+
| EmployeeId | EmployeeName | EmployeeSalary | EmployeeExperience |
+------------+--------------+----------------+--------------------+
| 1          | Larry        | 4500           | 5                  |
| 2          | Mike         | 130000         | 8                  |
| 3          | Sam          | 11000          | 5                  |
| 4          | Carol        | 140000         | 8                  |
+------------+--------------+----------------+--------------------+
4 rows in set (0.00 sec)

Following is the query to add a temporary column in MySQL where the values depend on another column. Here the temporary column is NewSalary −

mysql> select EmployeeId,EmployeeName,EmployeeSalary,EmployeeExperience,
case when
   EmployeeExperience=5 then EmployeeSalary+10000
   when EmployeeExperience=8 then EmployeeSalary+20000
else null
   end as NewSalary
from DemoTable;

This will produce the following output −

+------------+--------------+----------------+--------------------+-----------+
| EmployeeId | EmployeeName | EmployeeSalary | EmployeeExperience | NewSalary |
+------------+--------------+----------------+--------------------+-----------+
| 1          | Larry        | 4500           | 5                  | 14500     |
| 2          | Mike         | 130000         | 8                  | 150000    |
| 3          | Sam          | 11000          | 5                  | 21000     |
| 4          | Carol        | 140000         | 8                  | 160000    |
+------------+--------------+----------------+--------------------+-----------+
4 rows in set (0.00 sec)

Samual Sam

Updated on 30-Jul-2019 22:30:25

• Related Questions & Answers
• Add a temporary column with a value in MySQL?
• Add records from corresponding duplicate values in another column with MySQL
• Find the sum of a column values based on another numerical column in R.
• Update a column based on another MySQL table’s column
• Is it impossible to add a column in MySQL specifically before another column?
• Concatenate rows on the basis of boolean values in another column with MySQL
• Match column values on the basis of the other two column values in MySQL
• How do I select data from one table only where column values from that table match the column values of another table in MySQL?
• Add a new column and set values in it on the basis of conditions in MySQL?
• Performing total of a column in a temporary column in SAP
• Add a character in the end to column values with MySQL SELECT?
• MySQL query to select rows where column value is only 0, group by another column?
• How to find the column means of a column based on another column values that represent factor in an R data frame?
• MySQL query to group by column and display the sum of similar values in another column
• Can we add a column to a table from another table in MySQL?