Creating calculated fields in sql

Question

This will seem rudimentary but I can't find a concise example online that matches up.

I have three fields; m1, m2, and m3. I need to create a column or field that is the average of them three. The calculated field would be titled employment. Would the following code be suffice?

ALTER TABLE dbo.tablename ADD Employment AS Select ((m1+m2+m3)/3)

Sample data

m1   20    20    30
m2   15    17    25
m3   60    77    13

desired result.

Name        m1    m2    m3   Employment
Auto body    20    20    30     23
Auto Parts   15    17    25     19
Auto Sales   60    77    13     50

Answer 1

You are very close, it's called Computed Column

https://technet.microsoft.com/en-us/library/ms191250(v=sql.105).aspx

ALTER TABLE dbo.tablename ADD Employment AS ((m1+m2+m3)/3)

Update:

If you would like to force data type for a computed column, you could do the following

ALTER TABLE dbo.tablename ADD Employment AS CAST((m1+m2+m3)/3 AS Numeric (9,0))

Answer 2

You can check Computed Columns

CREATE TABLE t1(
    col1 int,
    col2 int,
    col3 int,
    col4 as (col1*col2*col3)/3

)

insert into t1  values(1,2,3)

select * from t1