user1282609 user1282609 - 4 months ago 11
SQL Question

calculate column based on other column values in sql

The result of sql select statement looks as below:

select * from table

Location Role Days

Hong Kong Senior Associate 48
Canberra Senior Associate 60
Sydney Senior Associate 55


I want to add a column to above result - Hours(there is no hours column in sql table)

Which should calculate Hours based on location

If Hong Kong then Days/7

If Canberra then Days/7.5

If Sydney then Days/7.25

The result should be like:

Location Role Days Hours

Hong Kong Senior Associate 48 6.85
Canberra Senior Associate 60 8
Sydney Senior Associate 55 7.586


I tried with select case which will check only one condition(if and else) and column should be in database.

But I want all to be condiitons to be checked in one sql statement and add hours column which is not in database.

any advice please.

Answer

A case solves this:

select location, role, days,
       (case when location = 'Hong Kong' then days / 7.0
             when location = 'Canberra' then days / 7.5
             when location = 'Sydney' then days / 7.25
        end) as hours
from t;

You can even add this column to the table using a computed column:

alter table t
    add hours as (case when location = 'Hong Kong' then days / 7.0
                       when location = 'Canberra' then days / 7.5
                       when location = 'Sydney' then days / 7.25
                  end);
Comments