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

Solution for How to use a calculated column to calculate another column in the same view
is Given Below:

I am hoping you can help with this question. I am using Oracle SQL (SQL Developer for this view)…

If I have a table with the following columns:

• ColumnA (Number)
• ColumnB (Number)
• ColumnC (Number)

In my view I have

``````Select
ColumnA,
ColumnB,
ColumnA + ColumnB As calccolumn1
``````

Now at this point, I want to use calccolumn1
but I cannot just say…

``````Select
ColumnA,
ColumnB,
ColumnA + ColumnB As calccolumn1
calccolumn1 / ColumnC as calccolumn2
``````

I am assuming I need some type of subquery..but this is where I need your help…
How would I word the query so that I can use calccolumn1 in another calculation within the same query? It might be an If then or a Case when, but bottomline it is some derived number.

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

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 have to include the expression for your calculated column:

``````SELECT
ColumnA,
ColumnB,
ColumnA + ColumnB AS calccolumn1
(ColumnA + ColumnB) / ColumnC AS calccolumn2
``````

In SQL Server

You can do this using With CTE

WITH common_table_expression (Transact-SQL)

``````CREATE TABLE tab(ColumnA DECIMAL(10,2), ColumnB DECIMAL(10,2), ColumnC DECIMAL(10,2))

INSERT INTO tab(ColumnA, ColumnB, ColumnC) VALUES (2, 10, 2),(3, 15, 6),(7, 14, 3)

WITH tab_CTE (ColumnA, ColumnB, ColumnC,calccolumn1)
AS
(
Select
ColumnA,
ColumnB,
ColumnC,
ColumnA + ColumnB As calccolumn1
from tab
)

SELECT
ColumnA,
ColumnB,
calccolumn1,
calccolumn1 / ColumnC AS calccolumn2
FROM  tab_CTE
``````

DBFiddle Demo