CODEWITHSUNDEEP
sql-serversql-server-2008
K.M.
K.M.

Reputation: 45

MS SQL Server - Use Calculated Field of SELECT statement

i would like to ask you if there is a statement to use calculated fields of the same SELECT-statement:

For example:

Table Test:

Machine Amount Value 500 20 20

SELECT Machine, 
    Amount*Value AS TestFormula
    TestFormula*12 AS TestFormulaYear
FROM Test

What is the correct statement to reuse this calculated field?

Thanks in advance,

Kevin

Upvotes: 1

Views: 6090

Answers (5)

cloudsafe
cloudsafe

Reputation: 2504

If you are looking to set up a Statement so that when formulas are changed many columns will be updated, I suppose you could declare the formulas and use Dynamic SQL. There can be an advantage to this if you want to be sure that lots of columns are updated correctly:

Declare @TestFormula as nvarchar(100) = '([Amount]*[Value])'
Declare @TestFormulaYear as nvarchar(100) = '(12*' + @TestFormula + ')'
declare @sql as nvarchar(max)
set @sql = 'SELECT [Machine], ' + @TestFormula + ' AS TestFormula, ' + @TestFormulaYear + ' AS TestFormulaYear
FROM (values(500, 20, 20)) a([Machine], [Amount], [Value])'
exec(@sql)

Upvotes: 0

HoneyBadger
HoneyBadger

Reputation: 15130

In sql server at least, you can do it with a subquery:

SELECT  Machine 
,       TestFormula
,       TestFormula*12 AS TestFormulaYear
FROM    (
           SELECT Machine 
           ,      Amount*Value AS TestFormula
           FROM   Test
        ) T

Upvotes: 2

Thom A
Thom A

Reputation: 95544

Assuming this is T-SQL:

You can't reference the alias of a column in the SELECT statement, no. If you look at SELECT (Transact-SQL) you'll note that the SELECT is the 8th part of the query to be processed. This means only ORDER BY is going to be able to reference a column's alias.

If you need to do further calculations on a calculated value you need to use a CTE, subquery, or redeclare the calculation. For example:

Repeated calculation:

SELECT [Column] * 10 As Expression,
       [Column] * 10 * 5 AS Expression2
FROM [Table];

CTE:

WITH Formula AS(
    SELECT [Column] * 10 As Expression
    FROM [Table])
SELECT Expression,
       Expression * 5 AS Expression2
FROM Formula;

Sub Query: 

SELECT Expression,
       Expression * 5 AS Expression2
FROM (SELECT [Column] * 10 As Expression
      FROM [Table]) Formula;

Upvotes: 0

Dan Guzman
Dan Guzman

Reputation: 46193

You can use a common-table expression (CTE) to reuse the value:

WITH formula AS (
    SELECT Machine, 
        Amount*Value AS TestFormula
    FROM Test
    )
SELECT Machine, 
    TestFormula
    TestFormula*12 AS TestFormulaYear
FROM formula;

If the batch with the CTE contains multiple statements, the preceding statement must be terminated with a semicolon.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

For the simple example you showed us, I would just recommend repeating the expression

SELECT
    Machine, 
    Amount*Value AS TestFormula,
    Amount*Value*12 AS TestFormulaYear
FROM Test;

Other answers have already shown how you can use a subquery to truly reuse the column, but that is not very performant compared to what I wrote above.

Upvotes: 1

Related Questions