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

January 26, 2022

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 futher 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?

>Solution :

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.