Add a temporary column in SQL where the values ​​depend on another column

Question

I have this table:

ID | name | result | 
--------------------    
 1 |  A   |   1    |
--------------------
 2 |  B   |   2    |
--------------------
 3 |  C   |   1    |
--------------------
 1 |  A   |   2    |
--------------------
 4 |  E   |   2    |
--------------------

I want to add a new temporary column next to | result |, and where result = 1 the value should be 100, and where result = 2 the value should be 80, so it should look like this:

ID | name | result | NewColumn|
-------------------------------    
 1 |  A   |   1    |  100     |
-------------------------------
 2 |  B   |   2    |   80     |
-------------------------------
 3 |  C   |   1    |  100     |
-------------------------------
 1 |  A   |   2    |  80      |
-------------------------------
 4 |  E   |   2    |  80      |
-------------------------------

How can I query this in SQL?

Answer 1

Use an CASEexpression in the column list SELECT- something like this:

SELECT
    ID,
    name,
    result,
    CASE
        WHEN result = 1 THEN 100
        WHEN result = 2 THEN 80
        ELSE NULL
    END AS NewColumn
FROM YourTable

Add additional expressions WHENor modify the expression ELSEas necessary.

Answer 2

You can add an operator to the request case:

SELECT id, name, result, CASE result WHEN 1 THEN 100 WHEN 2 THEN 80 ELSE NULL END
from   my_table

Answer 3

SELECT ID
,name
,result
,NewColumn = CASE WHEN result = 1 THEN 100 WHEN result = 2 THEN 80 END 
FROM Table1

Answer 4

, , . , , CASE. CASE .

. -

SELECT ID, name, result, (20*(6-result)) as NewColumn
FROM YourTable

, 1=>100, 2=>80, 3=>60, 4=>40, 5=>20.

, . .

Answer 5

IF Statement Mysql

SELECT col1, col2, col3, IF (col3 = 1, '100', IF (col3 = 2,80, '')) FROM your_table