Search code examples
sql-serveraveragealter

Creating calculated fields in sql

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
Solution

  • 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))