In the same view, how to use a calculated column to calculate another column

Problem

I’m hoping you can assist me with this query. I’m working with Oracle SQL (SQL Developer for this view)…

If I have the following columns in a table:

In my opinion, I have

Select  
ColumnA,
ColumnB,
ColumnA + ColumnB As calccolumn1

I’d like to use calccolumn1 at this point, but I can’t just say…

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

I’m guessing I’ll need a subquery…but this is where I’ll need your aid… How should I phrase the query so that calccolumn1 can be used in another calculation within the same query? It may be an If then or a Case when, but at the end of the day, it’s a derived integer.

Asked by Ewaver

Solution #1

A nested query could be used:

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

With a row of 3, 4, and 5 values, you get:

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

Unless it’s truly doing something expensive (through a function call, for example), you may just repeat the initial calculation:

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

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

Answered by Alex Poole

Solution #2

In Sql Server

This can be accomplished with the help of 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

Answered by Manoj

Solution #3

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 notice that the CROSS APPLY/OUTER APPLY statement is also accessible in other clauses:

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 method eliminates the need to wrap the entire query in an outerquery or copy/paste the same expression many times (with complex one it could be hard to maintain).

Related article: The SQL Language’s Most Missing Feature

Answered by Lukasz Szozda

Solution #4

The expression for your calculated column must be included:

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

Answered by swirlingsara

Solution #5

SQL Server is a database management system. This can be done with the use of 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

Answered by Vahid Heydarinezhad

Post is based on https://stackoverflow.com/questions/19185043/how-to-use-a-calculated-column-to-calculate-another-column-in-the-same-view