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

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?

CodePudding user response：

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.

CodePudding user response：

I would recommend a CTE for this:

with cte as
(
    select
        case
            when productA is not null then productA * 10
            else 0
        end newAColumn
        , case
            when productB is not null then productB * 5
            else 0
        end newBColumn
    from table1
)
select (newAcolumn\newBcolumn) as calc
    --, sum(newAColumn) --aggregate examples
    --, avg(newAColumn) --aggregate examples
from cte

I would also recommend adding an else to your case expressions if you want them included in something like AVG.

If you leave it as is, a row with productA of null will give a different average than a productA of 0.

CodePudding user response：

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