How to use a calculated column to calculate another column in the same view
In Sql Server
You can do this using cross apply
Select
ColumnA,
ColumnB,
c.calccolumn1 As calccolumn1,
c.calccolumn1 / ColumnC As calccolumn2
from t42
cross apply (select (ColumnA + ColumnB) as calccolumn1) as c
If you want to refer to calculated column on the "same query level" then you could use CROSS APPLY
(Oracle 12c):
--Sample data:
CREATE TABLE tab(ColumnA NUMBER(10,2),ColumnB NUMBER(10,2),ColumnC NUMBER(10,2));
INSERT INTO tab(ColumnA, ColumnB, ColumnC) VALUES (2, 10, 2);
INSERT INTO tab(ColumnA, ColumnB, ColumnC) VALUES (3, 15, 6);
INSERT INTO tab(ColumnA, ColumnB, ColumnC) VALUES (7, 14, 3);
COMMIT;
Query:
SELECT
ColumnA,
ColumnB,
sub.calccolumn1,
sub.calccolumn1 / ColumnC AS calccolumn2
FROM tab t
CROSS APPLY (SELECT t.ColumnA + t.ColumnB AS calccolumn1 FROM dual) sub;
DBFiddle Demo
Please note that expression from CROSS APPLY/OUTER APPLY
is available in other clauses too:
SELECT
ColumnA,
ColumnB,
sub.calccolumn1,
sub.calccolumn1 / ColumnC AS calccolumn2
FROM tab t
CROSS APPLY (SELECT t.ColumnA + t.ColumnB AS calccolumn1 FROM dual) sub
WHERE sub.calccolumn1 = 12;
-- GROUP BY ...
-- ORDER BY ...;
This approach allows to avoid wrapping entire query with outerquery or copy/paste same expression in multiple places(with complex one it could be hard to maintain).
Related article: The SQL Language’s Most Missing Feature
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