How to Use a Calculated Column by Another Calculated Column

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

Calculated column based on another calculated column

Could be the semicolons. You use semicolons or commas depending on cultural settings.

Try:

  =[DAYS]-DATEDIF([StartDate];[EndDate];"d") 

Or:

  =[DAYS]-DATEDIF([StartDate],[EndDate],"d") 

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
# <dbl> <dbl> <dbl> <dbl>
#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

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

How to include the result of the calculated column in another calculation with Iforels

The easiest solution is: to add columns with totals to your EBITDA Report unit as a relation and then use them in the "EBITDA" column in the same way you've calculated totals.

Iforels doesn't use SQL but you can try to reach the support team about that. The "Function" feature uses something similar to LaTeX "inline-math" and those guys should be able to help you to include references into the formula.

Calculated Column Based on Two Calculated Columns

Two issues going on here:

  • You can't use one column alias in another expression in the same SELECT list.

    However, you can establish aliases in a derived table subquery and use them in an outer query.

  • You can't do arithmetic with NULL, because NULL is not zero.

    However, you can "default" NULL to a non-NULL value using the COALESCE() function. This function returns its first non-NULL argument.

Here's an example:

SELECT *, count1+count2 AS total
FROM (SELECT *, COALESCE((subquery1), 0) AS count1,
COALESCE((subquery2), 0) AS count2
FROM ... ) t;

(remember that a derived table must be given a table alias, "t" in this example)



Related Topics



Leave a reply



Submit