Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2014
    Posts
    22

    Unanswered: Using Newly Created Column for doing further calcualtions in SAME Query

    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.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    why can't you do
    Code:
    Column_1 * (10*Column_1) as Column_6
    ?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Aug 2014
    Posts
    22
    Here is the working solution to this issue, provided by matak on another forum -

    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

    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...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

  4. #4
    Join Date
    Aug 2014
    Posts
    22
    Quote Originally Posted by rdjabarov View Post
    why can't you do
    Code:
    Column_1 * (10*Column_1) as Column_6
    ?
    Thanks for your reply rdjabarov.
    Ya, I had to use that solution.

    Regards

  5. #5
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    For large data sets you could use temp tables or sub queries.

    CTE's can hammer the database if you are pulling back large amounts of data.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •