Dummy Column in SQL Query

I am working on a SQL query and I need to do a calculation and store the result in dummy column in the output.

select NodeID, 
           count(distinct cpuindex) as number_of_cpu, 
           case 
               when count(distinct cpuindex) < 8 THEN 1
               else count(distinct cpuindex) / 8 
           end AS number_of_cores
    --number_of_cpu*number_of_cores*0.5 as Dummy_Column
    from CPUMultiLoad_Daily 
    where nodeid in (select nodeid from nodesdata)
    group by NodeID

I am doing the calculation as shown above however I am still missing something. Any help will be greatly appreciated.

Answer

SQL doesn’t allow for you to do computation on data being computed in the same query. As there is already an example with a CTE, here is what the subquery version might look like…

SELECT *, number_of_cpu*number_of_cores*0.5 as  Dummy_Column 
FROM (select NodeID, 
         count(distinct cpuindex) as number_of_cpu, 
          case 
               when count(distinct cpuindex) < 8 THEN 1
          else count(distinct cpuindex) / 8 
          end AS number_of_cores
     from CPUMultiLoad_Daily 
     where nodeid in (select nodeid from nodesdata)
     group by NodeID)

There are pros/cons between CTE and Subquery methods that you might consider depending on your specific case. You can read about it here Difference between CTE and SubQuery?