CODEWITHSUNDEEP
sqlsql-servert-sqlwindow-functions
Chinmoy Jyoti Kathar
Chinmoy Jyoti Kathar

Reputation: 1

How to refer to other columns using a condition when creating a calculated column?

Suppose I have a SQL table as shown below where Min Spend is the minimum spend for each year and is a calculated column created using SQL-Window Function

|------------|-------|--------|----------|
|   Year     |Month  |  Spend |Min Spend |
|------------|-------|--------|----------|
|    2018    |   Jan |  10    |   10     |
|    2018    |   Feb |  20    |   10     |
|    2018    |   Oct |  25    |   10     |
|    2019    |   Jan |  90    |   45     |
|    2019    |   Aug |  60    |   45     |
|    2019    |   Nov |  45    |   45     |
|------------|-------|--------|----------|

I would like to create a new column as a calculated field in the table that gives me the month corresponding the the 'Min Spend' for that year as shown below

|------------|-------|--------|----------|---------------|
|   Year     |Month  |  Spend |Min Spend |Min Spend Month|
|------------|-------|--------|----------|---------------|
|    2018    |   Jan |  10    |   10     |   Jan         |
|    2018    |   Feb |  20    |   10     |   Jan         |
|    2018    |   Oct |  25    |   10     |   Jan         |
|    2019    |   Jan |  90    |   45     |   Nov         |
|    2019    |   Aug |  60    |   45     |   Nov         |
|    2019    |   Nov |  45    |   45     |   Nov         |
|------------|-------|--------|----------|---------------|

Can anybody suggest how to approach this?

Upvotes: 0

Views: 64

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270793

You can use window functions like this:

select t.*,
       min(spend) over (partition by year) as min_spend,
       first_value(month) over (partition by year order by spend) as min_spend_month
from t;

Upvotes: 0

Related Questions