How to Use a Calculated Column to Calculate Another Column in the Same View

How to use a calculated column to calculate another column in the same view

You could use a nested query:

Select
  ColumnA,
  ColumnB,
  calccolumn1,
  calccolumn1 / ColumnC as calccolumn2
From (
  Select
    ColumnA,
    ColumnB,
    ColumnC,
    ColumnA + ColumnB As calccolumn1
  from t42
);

With a row with values 3, 4, 5 that gives:

   COLUMNA    COLUMNB CALCCOLUMN1 CALCCOLUMN2
---------- ---------- ----------- -----------
         3          4           7         1.4

You can also just repeat the first calculation, unless it's really doing something expensive (via a function call, say):

Select
  ColumnA,
  ColumnB,
  ColumnA + ColumnB As calccolumn1,
  (ColumnA + ColumnB) / ColumnC As calccolumn2
from t42; 

   COLUMNA    COLUMNB CALCCOLUMN1 CALCCOLUMN2
---------- ---------- ----------- -----------
         3          4           7         1.4 

How to use a calculated column to calculate another column in the same query using a subquery

You can use Temp_tables, Derived tables or Common table expressions (CTE) to obtain the result. Simple approach would be Derived table as you dont need much more coding.

  SELECT A.*
  ,  A.NLength/(A.NSpeedLimit * 88)  as [TravelTime]
  FROM
  (


  SELECT          
    sc.OBJECTID, 
    sn.Name, 
    case    when hn.side = 'Right Side' then ''
        else sc.LCity
    end as LCity, 
    case    when hn.side = 'Left Side' then ''
        else sc.RCity
    end as RCity, 
    case    when hn.side = 'Right Side' then ''
        else sc.LZip
    end as LZip, 
    case    when hn.side = 'Left Side' then ''
        else sc.RZip
    end as RZip, 
    sc.SHAPE.STLength() AS NLength,
    ISNULL(sc.SpeedLimit,1) AS NSpeedLimit

  FROM    STREETNAME AS sn 
   INNER JOIN
            STREETHASSTREETNAME AS hn ON 
                     sn.GlobalID = hn.GlobalID AND 
                     hn.Role = 'Primary'
          INNER JOIN      STREETCENTERLINE AS sc ON 
              hn.GlobalID = sc.GlobalID

            ) AS A

How to use calculated column value to another column in the same SQL Query

Well, you can't. There are ways to achieve what you want:

A.) using subquery

SELECT Column1, 
       TransactionType,
       CASE TransactionType WHEN 'Cancel' THEN EXPR 1.... 
         CASE TransactionType WHEN 'New' THEN EXPR 2 .... END AS CALCOLUMN2  
FROM
(

   SELECT COLUMN1
        ,CASE WHEN (SELECT CancelDate FROM TABLE3 WHERE EXPR....) <> '' THEN   'Cancel' ELSE 'New' END AS **TransactionType**   
   FROM .....
) ...

B.) using the expression itself

SELECT COLUMN1
        ,CASE WHEN (SELECT CancelDate FROM TABLE3 WHERE EXPR....) <> '' THEN 'Cancel' ELSE 'New' END AS TransactionType
        ,COLUMN2
        ,CASE (CASE WHEN (SELECT CancelDate FROM TABLE3 WHERE EXPR....) <> '' THEN 'Cancel' ELSE 'New' END) WHEN 'Cancel' THEN EXPR 1.... 
         CASE (CASE WHEN (SELECT CancelDate FROM TABLE3 WHERE EXPR....) <> '' THEN 'Cancel' ELSE 'New' END) WHEN 'New' THEN EXPR 2 .... END AS CALCOLUMN2     
FROM TABLE1
JOIN TABLE2 ....

How to use a calculated column by another calculated column

You need to use a sub-query.

SELECT c.d AS a, c.d + 2 AS b
FROM
  (SELECT 1+1 AS d) c

Result

| a | b |
---------
| 2 | 4 |

Oracle SQL Calculated Column - How do I reference a calculated column in another calculation in the same query?

In other Stack Overflow questions/responses, CROSS APPLY has come up. This is not working for me, since I have 10-20 calculated columns I need to create (all of which reference the other calculated columns).

CROSS APPLY could be chained:

SELECT t.*, s1.val1, s2.val2, s3.val3
FROM tab t
CROSS APPLY (SELECT t.val1 * t.val2 AS val3 FROM dual) s1
CROSS APPLY (SELECT s1.val3 * 100   AS val4 FROM dual) s2
CROSS APPLY (SELECT s2.val4 * 1000  AS val5 FROM dual) s3
--...

All you need is to make sure that you are referencing objects in correct order.

Related:

How to use a calculated column to calculate another column in the same view

Add a calculated column based on same and two other columns in r

Arrange the data based on descending order of year value and for each id subtract the current value with the next one.

library(dplyr)

dat %>%
  arrange(desc(year)) %>%
  group_by(id) %>%
  mutate(difference = value - lead(value)) %>%
  #to get 0 instead of NA use the below one
  #mutate(difference = value - lead(value, default = last(value))) %>%
  ungroup

#  year    id value difference
#          
#1  2011     1    10         -1
#2  2011     2    20         15
#3  2011     3    30         24
#4  2005     1    11         NA
#5  2005     2     5         NA
#6  2005     3     6         NA

How to use calculated field in another field of the same query

Do it like this:

;WITH YourCTE AS
(
Select
    Case ...
        When ... then ...
        When ... then ...
        When ... then ...
    End                     as FieldA
From TblSource
)
SELECT FieldA, FieldA + 1 AS FieldB, FieldA + 2 AS FieldC ....
FROM YourCTE

---

Related Topics