CODEWITHSUNDEEP
mysqlsqlsql-server
GaRaGe
GaRaGe

Reputation: 67

Adding a new column in SQL which calculates value from other columns

I was trying to create a new column in SQL which has calculated values from other columns in the same table.

Please see the case below

col1 col2
A    1   
A    2
B    1
B    2
B    3

I need to create a new column 'col3' that finds the max of col2 grouped by col1. So the result i want is

col1 col2 col3
A    1    2   
A    2    2
B    1    3
B    2    3
B    3    3

I would like to know if a query can be something along the below lines

ALTER TABLE table1 ADD col3 AS (SELECT max(col2) GROUPBY col1)

Upvotes: 1

Views: 4605

Answers (1)

Rahul
Rahul

Reputation: 77866

You don't need an ALTER statement and can get the extra column in your SELECT query like

select col1, col2, max(col2) as col3
from tbl1
group by col1

Upvotes: 3

Related Questions