5

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
TT.
  • 15,774
  • 6
  • 47
  • 88
Tim Wilcox
  • 1,275
  • 2
  • 19
  • 43

2 Answers2

8

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))
EricZ
  • 6,065
  • 1
  • 30
  • 30
  • Ok, to add on. I forgot that my computed field/column needs to be numeric (9). Would I add it in as such ALTER TABLE dbo.tablename ADD Employment Numeric (9) AS ((m1+m2+m3)/3) – Tim Wilcox Sep 07 '17 at 21:26
7

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
Yeou
  • 622
  • 1
  • 7
  • 21