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
Postgresql - Order by an Array
Maximum and Minimum Number of Tuples in Natural Join
Dba_Jobs_Running: Table or View Does Not Exist When Trying to Access from Procedure
How to Make a JPA Query with Left Outer Join
SQL Server, Combining Like and In
How to Change All SQL Columns of One Datatype into Another
Change Separator of Wm_Concat Function of Oracle 11Gr2
SQL Identity with Leading Padded Zeros
What Happens with Duplicates When Inserting Multiple Rows
Finding Continuous Ranges in a Set of Numbers
How to Prevent SQL Injection in Wordpress
Get Every Hour for a Time Range
Ms Access: How to Count Distinct Value Using Access Query
How to Re-Use Result for Select, Where and Order by Clauses
Reverse- Geocoding: How to Determine the City Closest to a (Lat,Lon) with Bigquery SQL