Using Newly Created Column for doing further calcu

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using Newly Created Column for doing further calcu

Author

Topic

happy1001
Starting Member

4 Posts

Posted - 2015-04-12 : 19:44:07

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.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-04-12 : 20:03:54

Either calculate the whole thing:

Column_1*10*Column_2 As Column_6

or wrap your query in as a subquery:


select *
      ,Column_1*Column_5 As Column_6
  from (
        your original query here
       ) as a

happy1001
Starting Member

4 Posts

Posted - 2015-04-13 : 02:55:00

Thank you bitsmed for your reply. The method of subquery solves the issue.

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 -

quote:

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources