Dummy Column in SQL Query

Question

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.

You cannot re-use a column alias in the same `select` where it is defined You need to repeat the expression, use a CTE, or use a subquery. — Gordon Linoff, Jul 20 '20 at 21:11
What do you mean by missing something? What should it be doing and what isn't it doing? Sample data and expected results would help. — Dale K, Jul 20 '20 at 21:11

score 2 · Answer 1 · answered Jul 20 '20 at 21:16

Below the correct query using a CTE.

;with CTE as (
     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
)
select *, number_of_cpu*number_of_cores*0.5 as  Dummy_Column
from CTE

score 2 · Accepted Answer · answered Jul 20 '20 at 21:25

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?

Thank you, I was able to update my query as you mentioned. Is it able to display the output as Integer and not decimal? — SQL Novice, Jul 21 '20 at 19:28

Dummy Column in SQL Query

2 Answers2