Using Newly Created Column for doing further calcualtions in SAME Query

Question

Using Newly Created Column for doing further calcualtions in SAME Query

tlp6327
SSC-Addicted

Points: 408
More actions
April 12, 2015 at 5:44 pm

#317392

Hi

I am new to SQL Programming. I am learning the basics. I am trying to create a simple query like this -

SELECT

Column_1,

Column_2,

Column_3,

10*Column_1 AS Column_4,

10*Column_2 AS Column_5,

-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6

FROM Table_1

First 3 Columns are available within the Original Table_1

The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.

Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.

I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case, or am I doing something wrong here ?

If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution for me ?

Please suggest the various options possible for doing this.

I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me.And now I have to do various calculations that involve making use of both these type of columns.

If I have not been able to make myself clear then please tell and I will give more examples.

Thanks a lot for any help.


          
          
           
            
             
              andrew.courneya 63072
             
            
            
             
             
             
              
              
             
             
              Grasshopper
             
             
              Points: 17
             
             
              
               
                
                 More actions
                
               
              
              
               
               
              
              
              
             
             
             
            
            
            
            
             
             
             
              
               
                April 13, 2015 at 2:49 pm
               
              
              
               
               
               
                #1790634
               
              
              
              
              
               
                
               
              
             
             
             
             
             
             
              
               You can also do simple wraps/nests right in the source query. Think of it as an action on the data in the table that you've wrapped (in this case, table a is already extracted, but doesn't have the extra math done, so extract that data as you need it, wrap it, do new maths). As in:
              
              
               SELECT a.*, (a.Column_1*a.Column_5) as Column_6
              
              
               FROM (
              
              
               SELECT
              




    

              
               Column_1,
              
              
               Column_2,
              
              
               Column_3,
              
              
               10*Column_1 AS Column_4,
              
              
               10*Column_2 AS Column_5,
              
              
               -- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6
              
              
               FROM Table_1
              
              
               ) as a
              
              
               I use these pretty frequently, as they don't seem to harsh the optimizer too bad, and they are easily readable even by SQL noobs.
              
             
             
            
           
          
          
          
          
          
           
            
             
              tlp6327
             
            
            
             
             
             
              
              
             
             
              SSC-Addicted
             
             
              Points: 408
             
             
              
               
                
                 More actions
                
               
              
              
               
               
              
              
              
             
             
             
            
            
            
            
             
             
             
              
               
                April 14, 2015 at 6:23 am
               
              
              
               
               
               
                #1790765
               
              
              
              
              
               
                
               
              
             
             
             
             
             
             
              
               
                
                 
                  gward 98556 (4/13/2015)
                 
                
                
                I sometimes use OUTER APPLY to avoid repeating a formula instead of using a CTE, but I'm not sure which method is more efficient. For example:
                
                 
                 
                
                
                 
                  SELECT
                 
                
                
                 
                  Column_1,
                 
                
                
                 
                  Column_2,
                 
                
                
                 
                  Column_3,
                 
                
                
                 
                  10*Column_1 AS Column_4,
                 
                
                
                 
                  OA.Column_5,
                 
                
                
                 
                  Column_1*OA.Column_5 As Column_6
                 
                
                
                 
                  FROM Table_1
                 
                
                
                 
                  OUTER APPLY (SELECT Column_5=10*Table_1.Column_2) OA
                 
                
                
                
               
               
               
              
              
               
                Thank you so much gward 98556 for providing this alternative method.
               
              
              
               
                Regards
               
              
              
              
             
             
              
             
            
           
          
          
          
          
           
           
            
             
              
               tlp6327
              
             
             
              
              
              
               
               
              
              
               SSC-Addicted
              
              
               Points: 408
              
              
               
                
                 
                  More actions
                 
                
               
               
                
                
               
               
               
              
              
              
             
             
             
             
              
              
              
               
                
                 April 14, 2015 at 6:24 am
                
               
               
                
                
                
                 #1790767
                
               
               
               
               
                
                 
                
               
              
              
              
              
              
              
               
                
                 
                  
                   andrew.courneya 63072 (4/13/2015)
                  
                 
                 
                 You can also do simple wraps/nests right in the source query. Think of it as an action on the data in the table that you've wrapped (in this case, table a is already extracted, but doesn't have the extra math done, so extract that data as you need it, wrap it, do new maths). As in:
                 
                  SELECT a.*, (a.Column_1*a.Column_5) as Column_6
                 
                 
                  FROM (
                 
                 
                  SELECT
                 
                 
                  Column_1,
                 
                 
                  Column_2,
                 
                 
                  Column_3,
                 
                 
                  10*Column_1 AS Column_4,
                 
                 
                  10*Column_2 AS Column_5,
                 
                 
                  -- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6
                 
                 
                  FROM Table_1
                 
                 
                  ) as a
                 
                 
                  I use these pretty frequently, as they don't seem to harsh the optimizer too bad, and they are easily readable even by SQL noobs.
                 
                
                
                
               
               
                Wow ! There are so many ways to do this. I was not aware. Thank you so much andrew.courneya 63072 for sharing this approach.
               
               
                Regards
               
              
              
             
            
           
           
           
           
           
            
             
              
               gward 98556
              
             
             
              
              
              
               
               
              
              
               Mr or Mrs. 500
              
              
               Points: 538
              
              
               
                
                 
                  More actions
                 
                
               
               
                
                
               
               
               
              
              
              
             
             
             
             
              
              
              
               
                
                 April 14, 2015 at 6:38 am
                
               
               
                
                
                
                 #1790775
                
               
               
               
               
                
                 
                
               
              
              
              
              
              
              
               
                For very simple calculations like your example, it's normally fine to simply do the whole calculation in the SELECT part of the query, accepting that there is a little bit of repetition between the column calculations. However, I think if you have a very complex calculation that is involved in multiple calculated columns in the SELECT clause, or in the WHERE clause or even a JOIN clause, then it is nicer to use a CTE or an OUTER APPLY, because then you only have the calculation's definition in one place. This makes the query safer to maintain in future, because there is always a risk that someone might edit the query, and they will find and improve the calculation in the SELECT but not scroll down and see that the same calculation also appeared somewhere else, such as the WHERE clause way down at the bottom, and suddenly the query won't work the way it way intended. Using a CTE or an OUTER APPLY effectively gives you an alias name for the calculation that can be reused in as many places as needed, without writing the whole calculation again.
               
              
              
             
            
           
           
           
           
           
            
             
              
               tlp6327
              
             
             
              
              
              
               
               
              
              
               SSC-Addicted
              
              
               Points: 408
              
              
               
                
                 
                  More actions
                 
                
               
               
                
                
               
               
               
              
              
              
             
             
             
             
              
              
              
               
                
                 April 14, 2015 at 9:02 am
                
               
               
                
                
                
                 #1790845
                
               
               
               
               
                
                 
                
               
              
              
              
              
              
              
               
                
                 
                  
                   gward 98556 (4/14/2015)
                  
                 
                 
                 For very simple calculations like your example, it's normally fine to simply do the whole calculation in the SELECT part of the query, accepting that there is a little bit of repetition between the column calculations. However, I think if you have a very complex calculation that is involved in multiple calculated columns in the SELECT clause, or in the WHERE clause or even a JOIN clause, then it is nicer to use a CTE or an OUTER APPLY, because then you only have the calculation's definition in one place. This makes the query safer to maintain in future, because there is always a risk that someone might edit the query, and they will find and improve the calculation in the SELECT but not scroll down and see that the same calculation also appeared somewhere else, such as the WHERE clause way down at the bottom, and suddenly the query won't work the way it way intended. Using a CTE or an OUTER APPLY effectively gives you an alias name for the calculation that can be reused in as many places as needed, without writing the whole calculation again.
                
                
                
               
               
                That makes perfect sense gward. In that situation CTE or OUTER APPLY would be better.
               
               
                Thanks again.

Viewing 10 posts - 1 through 10 (of 10 total)


           
            
             
              
               
               
              
              
               
                You must be logged in to reply to this topic.
                
                 Login to reply

Answer 1

Hi,

You cant reference a column alias in the same select statement it was created.

You will either need to do Column1*10*column2 as Column6

or wrap it in a cte first

with cte as (

SELECT

Column_1,

Column_2,

Column_3,

10*Column_1 AS Column_4,

10*Column_2 AS Column_5

)

select *, column1*column5 as Column6

from cte

Answer 2

matak (4/12/2015)

Hi,
You cant reference a column alias in the same select statement it was created.

You will either need to do Column1*10*column2 as Column6

or wrap it in a cte first

with cte as (

SELECT

Column_1,

Column_2,

Column_3,

10*Column_1 AS Column_4,

10*Column_2 AS Column_5

)

select *, column1*column5 as Column6

from cte

Thank you so much for providing the solution matak. That works perfectly.

For other newbies like me, who might be wondering why the original query does not work automatically without using any CTE method etc. the explanation is given in this article -

http://sqlmag.com/blog/tip-apply-and-reuse-column-aliases

SQL is a language with many unique aspects. One of those is the fact that the logical order in which the various query clauses are evaluated is different than the keyed-in order. The keyed-in order of a query’s clauses is:

a. SELECT

b. FROM

c. WHERE

d. GROUP BY

e. HAVING

f. ORDER BY

But the logical query processing order is:

1. FROM

2. WHERE

3. GROUP BY

4. HAVING

5. SELECT -- column aliases created here

6. ORDER BY

Due to this special design a column alias assigned in the SELECT phase is not visible to preceding logical query processing phases.

Thanks and regards

Answer 3

@ Moderators

I made a reply to the above post, few hours ago. But that post seems to have become invisible now !

Can you please make that post visible, or I will have to post the reply again ?

Thanks a lot

----- EDIT -----

Since my reply was not posted by the moderators, I am typing that reply again here.

matak (4/12/2015)

Hi,
You cant reference a column alias in the same select statement it was created.

You will either need to do Column1*10*column2 as Column6

or wrap it in a cte first

with cte as (

SELECT

Column_1,

Column_2,

Column_3,

10*Column_1 AS Column_4,

10*Column_2 AS Column_5

)

select *, column1*column5 as Column6

from cte

Thank you so much for providing the solution matak. That works perfectly.

For other newbies like me, who might be wondering why the original query does not work automatically without using any CTE method etc. the explanation is given in this article -

http://sqlmag.com/blog/tip-apply-and-reuse-column-aliases

SQL is a language with many unique aspects. One of those is the fact that the logical order in which the various query clauses are evaluated is different than the keyed-in order. The keyed-in order of a query’s clauses is:

a. SELECT

b. FROM

c. WHERE

d. GROUP BY

e. HAVING

f. ORDER BY

But the logical query processing order is:

1. FROM

2. WHERE

3. GROUP BY

4. HAVING

5. SELECT -- column aliases created here

6. ORDER BY

Due to this special design a column alias assigned in the SELECT phase is not visible to preceding logical query processing phases.

Thanks and regards

Answer 4

I sometimes use OUTER APPLY to avoid repeating a formula instead of using a CTE, but I'm not sure which method is more efficient. For example:

SELECT

Column_1,

Column_2,

Column_3,

10*Column_1 AS Column_4,

OA.Column_5,

Column_1*OA.Column_5 As Column_6

FROM Table_1

OUTER APPLY (SELECT Column_5=10*Table_1.Column_2) OA

Answer 5

You can also do simple wraps/nests right in the source query. Think of it as an action on the data in the table that you've wrapped (in this case, table a is already extracted, but doesn't have the extra math done, so extract that data as you need it, wrap it, do new maths). As in:

SELECT a.*, (a.Column_1*a.Column_5) as Column_6

FROM (

SELECT

Column_1,

Column_2,

Column_3,

10*Column_1 AS Column_4,

10*Column_2 AS Column_5,

-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6

FROM Table_1

) as a

I use these pretty frequently, as they don't seem to harsh the optimizer too bad, and they are easily readable even by SQL noobs.

Answer 6

gward 98556 (4/13/2015)

I sometimes use OUTER APPLY to avoid repeating a formula instead of using a CTE, but I'm not sure which method is more efficient. For example:

SELECT

Column_1,

Column_2,

Column_3,

10*Column_1 AS Column_4,

OA.Column_5,

Column_1*OA.Column_5 As Column_6

FROM Table_1

OUTER APPLY (SELECT Column_5=10*Table_1.Column_2) OA

Thank you so much gward 98556 for providing this alternative method.

Regards

Answer 7

andrew.courneya 63072 (4/13/2015)

You can also do simple wraps/nests right in the source query. Think of it as an action on the data in the table that you've wrapped (in this case, table a is already extracted, but doesn't have the extra math done, so extract that data as you need it, wrap it, do new maths). As in:
SELECT a.*, (a.Column_1*a.Column_5) as Column_6

FROM (

SELECT

Column_1,

Column_2,

Column_3,

10*Column_1 AS Column_4,

10*Column_2 AS Column_5,

-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6

FROM Table_1

) as a

I use these pretty frequently, as they don't seem to harsh the optimizer too bad, and they are easily readable even by SQL noobs.

Wow ! There are so many ways to do this. I was not aware. Thank you so much andrew.courneya 63072 for sharing this approach.

Regards

Answer 8

For very simple calculations like your example, it's normally fine to simply do the whole calculation in the SELECT part of the query, accepting that there is a little bit of repetition between the column calculations. However, I think if you have a very complex calculation that is involved in multiple calculated columns in the SELECT clause, or in the WHERE clause or even a JOIN clause, then it is nicer to use a CTE or an OUTER APPLY, because then you only have the calculation's definition in one place. This makes the query safer to maintain in future, because there is always a risk that someone might edit the query, and they will find and improve the calculation in the SELECT but not scroll down and see that the same calculation also appeared somewhere else, such as the WHERE clause way down at the bottom, and suddenly the query won't work the way it way intended. Using a CTE or an OUTER APPLY effectively gives you an alias name for the calculation that can be reused in as many places as needed, without writing the whole calculation again.

Answer 9

gward 98556 (4/14/2015)

For very simple calculations like your example, it's normally fine to simply do the whole calculation in the SELECT part of the query, accepting that there is a little bit of repetition between the column calculations. However, I think if you have a very complex calculation that is involved in multiple calculated columns in the SELECT clause, or in the WHERE clause or even a JOIN clause, then it is nicer to use a CTE or an OUTER APPLY, because then you only have the calculation's definition in one place. This makes the query safer to maintain in future, because there is always a risk that someone might edit the query, and they will find and improve the calculation in the SELECT but not scroll down and see that the same calculation also appeared somewhere else, such as the WHERE clause way down at the bottom, and suddenly the query won't work the way it way intended. Using a CTE or an OUTER APPLY effectively gives you an alias name for the calculation that can be reused in as many places as needed, without writing the whole calculation again.

That makes perfect sense gward. In that situation CTE or OUTER APPLY would be better.

Thanks again.