How to do further analysis on the fresh CASE Statement column in SQL?

Question

When I use CASE statements

Example - In this table

and I want to run this query below

SELECT 
    *,
    CASE
        WHEN productA IS NOT NULL
            THEN productA * 10
    END AS newAcolumn,
    CASE
        WHEN productB IS NOT NULL
            THEN productB * 5
    END AS newBcolumn
FROM
    table1

I want to perform further aggregate function and calculations on the new columns. Example

(newAcolumn / newBcolumn) as calc

How can I do that? Would I have create a new table altogether?

Answer 1

You could wrap it in a sub-query

SELECT *
, newAcolumn/newBcolumn AS newCcolumn
FROM
(
    SELECT *,
    CASE
    WHEN productA is not null THEN productA*10
    END as newAcolumn,
    CASE
    WHEN productB is not null THEN productB*5
    END as newBcolumn
    FROM table1
) Q;

Or use a CTE, which is like a re-usable template of a sub-query.

WITH CTE AS (
    SELECT *,
    CASE
    WHEN productA is not null THEN productA*10
    END as newAcolumn,
    CASE
    WHEN productB is not null THEN productB*5
    END as newBcolumn
    FROM table1
)
SELECT *
, newAcolumn/newBcolumn AS newCcolumn
FROM CTE

Or use an OUTER/CROSS APPLY

SELECT t.*
, a.newAcolumn
, a.newBcolumn
, a.newAcolumn/a.newBcolumn AS newCcolumn
FROM table1 t
OUTER APPLY (
    SELECT 
    CASE
    WHEN productA is not null THEN productA*10
    END as newAcolumn,
    CASE
    WHEN productB is not null THEN productB*5
    END as newBcolumn
) a