CODEWITHSUNDEEP
mysqlsql
vikrampandit993
vikrampandit993

Reputation: 3

mysql sql case statement gives same value for different conditions

I am trying a simple case query in W3School page.

http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_groupby_2

When I run following query, I keep getting 'over' for all range values. It should show 'under' for price below 500.

SELECT 
(o.Quantity* p.Price) as price,
case price when price< 500 
           then 'under' 
           else 'over' 
           end as range 
 FROM OrderDetails o 
 inner join Products p 
 on (o.ProductID = p.ProductID)

Upvotes: 0

Views: 219

Answers (1)

radar
radar

Reputation: 13425

AS price column exists in Products table, it is using that value

you can't reference the column alias price in the same select statement, you need to repeat the price calculation

Also you are mixing two kinds of case expression

you have 

case price when price <500

you should use

case when (o.Quantity* p.Price) < 500

Upvotes: 1

Related Questions