SQL select statement that creates calculated column, then uses calculated column for another calcula-CodePudding

I need to use a calculated column for another column.

Here's what I have so far:

SELECT item_name, 
       list_price,
       discount_percentage, 
       list_price * discount_percentage AS discount_amount,
       list_price * discount_percentage - discount_amount AS discount_price,
FROM items
ORDER BY discount_price DESC

This comes up as incorrect due to my trying to use the calculated column to create another calc column

I would like to use a select statement for this, so I have not researched any other ways. Does anyone see any obvious errors? I am new to SQL

CodePudding user response：

You'll need a table expression to "name" an expression you create. Then you can use it in the outer query, as shown below:

For example:
select
  *,
  list_price * discount_percentage - discount_amount AS discount_price,
from (
  SELECT item_name, 
       list_price,
       discount_percentage, 
       list_price * discount_percentage AS discount_amount
  FROM items
) x
ORDER BY discount_price DESC

CodePudding user response：

In MySQL, you can use inline variables to accomplish it. First, you can declare a variable with the @ and assign based on := and that is the result column as name assigned. THEN, that @variable is available to use in your subsequent column select. To declare the variable, just set based in the FROM clause as an alias, then continue as normal.

SELECT item_name, 
       list_price,
       discount_percentage, 
       -- assuming the discount percentage of 10% would be .10, not 10.
       -- if 10% = 10.0, then do ( discount_percentage / 100.0 )
       @myDiscount := list_price * discount_percentage AS discount_amount,
       list_price - @myDiscount AS discount_price
   FROM 
      items,
      ( select @myDiscount := 0.0 ) sqlvars
   ORDER BY 
      discount_price DESC

Since the SQLVars alias would only ever return 1 record, we can just have as a comma separated additional table, no JOIN clause required. That declares the variable.

Then, in the COLUMNS table, you can see how it is FIRST calculated per by calculating the discount amount and assigning to the @myDiscount VARIABLE, but stores into the final result column DISCOUNT_AMOUNT. Then, the VARIABLE can be used as a direct subtraction from the list price for the discunt price.

No need to select from a select result