SQL: Creating temporary variables

Question

I'm new to SQL so please consider this noob question. Also, its so embarrassing to admit that I cannot search for the right keyword in Google and I'm running out of time so I decided to ask it here.

Code:

select
 *,
 price * quantity as [Total price],
 case
   when [Total price]>100 and [Total price]<= 200 then '2%'
   when [Total price]>200 and [Total price]<= 300 then '3%'
   when [Total price]>300 and [Total price]<= 400 then '4%'
   else '0%'
 end as tax
from
grocery

As you can see, what I am trying to do is try to create a temporary variable on execution of SQL statement but, this one gives me error

Error 1: could not prepare statement [1 no such column: Total price]

How could I do this ?

Answer 1

A quick way would be to use a CTE (Common Table Expression). This allows you to pre-calculate some values and then refer the values in the body of the query.

If a statement with a CTE isn't the first thing in a batch, you need to end the preceding stuff with a ;:

 ;
 With Totals as
 (
    select  *,
            price * quantity as [Total price],
    from    grocery
 )
 select  *
    ,  case
         when [Total price]>100 and [Total price]<= 200 then '2%'
         when [Total price]>200 and [Total price]<= 300 then '3%'
         when [Total price]>300 and [Total price]<= 400 then '4%'
        else '0%'
     end as tax
 from
   Totals

The root of your problem, by the way, is that you can't define an expression and then use the expression by name in a query. You can re-use the expression, but not by name:

select  x + 1 as Expr1
   ,    (x + 1) * 2  as Expr2
from   Table1

That will work, but the following won't work:

select x + 1 as Expr1
   ,   Expr1 * 2 as Expr2   -- This won't work
from   Table1

Answer 2

SELECT *,
 case
   when [Total price]>100 and [Total price]<= 200 then '2%'
   when [Total price]>200 and [Total price]<= 300 then '3%'
   when [Total price]>300 and [Total price]<= 400 then '4%'
   else '0%'
 end as tax
FROM (SELECT *, price * quantity as [Total price]
      FROM grocery) A

OR

select
 *,
 price * quantity as [Total price],
 case
   when price * quantity >100 and price * quantity<= 200 then '2%'
   when price * quantity>200 and price * quantity<= 300 then '3%'
   when price * quantity>300 and price * quantity<= 400 then '4%'
   else '0%'
 end as tax
from
grocery