Creating calculated fields in sql

sql-server average alter
Tim Wilcox picture Tim Wilcox · Sep 7, 2017 · Viewed 7k times · Source

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

Yeou picture Yeou · Sep 7, 2017

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

Related questions

Avg of a Sum in one query

I would like to know if I can get the average of a sum in one single SQL SERVER request, Have tried to do it with the following request but it doesn't work: SELECT t.client, AVG(SUM(t.asset)) …

Read More
Average of grouped rows in Sql Server

I have an Sql Server Table.. it's something like this: Id ...... Column1 ...... Column2 ```````````````````````````````` 1 ........ 1 ............. 34 2 ........ 1 ............. 44 3 ........ 2 ............. 45 4 ........ 2 ............. 36 5 ........ 2 ............. 23 6 ........ 3 ............. 68 7 ........ 3 ............. 26 So, I need to select the average of Column2,but group with column1 before doing that. I mean, if I say Avg(Column2) it just returns a …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More