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

Question

When I use CASE statements

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?

`CASE`, in T-SQL, is an **expression** not a statement. It returns a scalar value, it does not control the logical flow operations of the batch/query. As for the question, you could use a derived table; a CTE or a subquery. — Thom A, Jan 26 '22 at 20:28
It may also help to generate a default value for your `CASE` statements using `ELSE` — Peter Smith, Jan 26 '22 at 20:30
Does this answer your question? [Using alias in query and using it](https://stackoverflow.com/questions/2055126/using-alias-in-query-and-using-it) — Stu, Jan 26 '22 at 20:31

Paul Alan Taylor · Accepted Answer · 2022-01-26T20:40:40.777

You have a couple of options if you want to avoid repeating the syntax. There may be more. Here are my suggestions.

You could store your query results in a temporary table, then query on that temporary table.

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
-- Look here!  Creating a temp table on the fly.
INTO 
   #tt
from table1;
-- Temp table exists now.
-- Query at will!
SELECT newBColumn / newAColumn FROM #tt;

You can also use CTEs, which will achieve the same end.

WITH tt 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
)
-- a CTE called tt has just been created and can
-- be queried like a table
SELECT newAColumn / newBColumn FROM tt;

Which one you use depends on what you need. If you want to perform repeated queries on data that won't change much, use a temporary table.

If you need data to be absolutely up to the minute and don't mind making the query to order every time, use a CTE.

LukStorms · Answer 2 · 2022-01-26T20:44:31.853

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

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

2 Answers2