Adding Simple Calculations to SQL Query-CodePudding

I would like to add a few columns that will calculate columns from a query. I tried adding it towards the end of the query but it errors out at the "AS" when I am naming the column. This query works without the calculation step, so it may be because the calculation should be done elsewhere?

Thank you all in advance for any help!

     SELECT * 
        FROM (SELECT DISTINCT   
         col_1,
         col_2,
         col_3,
         col_x
    CASE
         WHEN col_1 = '' THEN col_x     
           ELSE col_2 
    END AS FINISHEDCASE, 
    col_1, 
    col_2, 
    col_3,
    col_x, 
    (calc_1*(1-(calc_2/100)) AS calc_column 
         [**Desire to
         add several columns that reference selected columns, ie,col_1, 
         col_2, etc. onto the
         end of the query**]
    
    FROM TABLE_NAME 
      LEFT JOIN TABLE_NAME_2 
    ON col_a = col_b
          )as subquery

WHERE Fieldname NOT IN ('D')

The expected result of the query should look as follows

col_1, col_2, col_3, col_x, calc_column

Running the query right now gives an error message at the 'AS' clause.

CodePudding user response：

It's a lot of guess work what you really intend to do but as I understood it, you like to calculate two calculated values (calc_1 and calc_2) and then use them as an intermediate result to further calculate a column (calc_column). That could be done like this:

WITH Calculated AS (SELECT col_1, col_2, col_3, col_x, (col_1   col_2) AS calc_1, (col_3 * col_4) AS calc_2 FROM TABLE_NAME)
SELECT col_1, col_2, col_3, col_x, (calc_1*(1-(calc_2/100))) AS calc_column FROM Calculated

CodePudding user response：

Another method is to use CROSS APPLY with VALUES. Each calculation can then be returned:

SELECT ...
  FROM ...
 CROSS APPLY (VALUES (formula1, formula2)) AS c(calc_1, calc_2)
 WHERE ...

You can then use the calculations anywhere else in the query as needed. You can even stack CROSS APPLY to calculate variables that can then be used in following CROSS APPLY statements. For example:

SELECT ...
  FROM ...
 CROSS APPLY (VALUES (col1   col2, col3   col4)) AS v(a, b)
 CROSS APPLY (VALUES (v.a * v.b)) AS e(c)