Need to resolve the query

Question

The management of EasyShop would like to classify the items as cheap, affordable, expensive and very expensive. The classification is done if item unit price is between 0 and 499 then 'Cheap', if between 500 and 1999 then 'Affordable', if between 2000 and 4999 then 'Expensive' and if price is more than or equal to 5000 then 'Very Expensive'. Write a query to display the itemtype and classification of items. Display unique rows sorted by itemtype and classification in ascending order.

This is my query

SELECT DISTINCT ItemType,
CASE
Price BETWEEN 0 AND 499 THEN 'Cheap'
Price BETWEEN 500 AND 1999 THEN 'Affordable'
Price BETWEEN 2000 AND 4999 THEN 'Expensive'
Price >=5000 THEN 'Very Expensive'
END Classification
FROM Item

I think you need to add an `ORDER BY` and probably remove the `DISTINCT`. — Gordon Linoff, Mar 05 '16 at 13:48
Yes order by is needed.I will add it later.First i need to remove the misplaced FROM. — Shirley, Mar 05 '16 at 14:25
Please have a look at this similar question http://stackoverflow.com/questions/14189216/case-in-select-statement — Amitd, Mar 05 '16 at 15:13

score 0 · Answer 1 · edited Mar 05 '16 at 14:50

0

Are you trying something like the below? Check it out:

SELECT DISTINCT CASE 
WHEN Price BETWEEN 0 AND 240 THEN 'Cheap'
WHEN Price BETWEEN 241 AND 1200 THEN 'Expensive' END AS Range 
FROM Products 
ORDER BY Price

edited Mar 05 '16 at 14:50

answered Mar 05 '16 at 14:34

DOTNET Rocker

37
7

Could you write what is the error or any idea?? What is not working?? – DOTNET Rocker Mar 05 '16 at 14:46

score 0 · Answer 2 · answered May 14 '16 at 09:27

You need to add ORDER BY ItemType, Classification to your query. You should also include WHEN in your CASE statement.

SELECT DISTINCT ItemType,
 CASE WHEN
      Price BETWEEN 0 AND 499 THEN 'Cheap'
      Price BETWEEN 500 AND 1999 THEN 'Affordable'
      Price BETWEEN 2000 AND 4999 THEN 'Expensive'
      Price >=5000 THEN 'Very Expensive'
 END Classification
 FROM Item
 ORDER BY ItemType, Classification

score 0 · Answer 3 · edited May 10 '19 at 11:35

There is a syntax error in the Query. CASE Syntax

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

The following would be the correct query.

SELECT DISTINCT ItemType, CASE 
WHEN Price BETWEEN 0 AND 499 THEN 'Cheap'
WHEN Price BETWEEN 500 AND 1999 THEN 'Affordable'
WHEN Price BETWEEN 2000 AND 4999 THEN 'Expensive'
WHEN Price >=5000 THEN 'Very Expensive' END Classification
FROM Item
ORDER BY ItemType, Classification;

yeshwantreddy kesari · Answer 4 · 2019-06-18T14:31:17.210

0

The following would be the correct query in ORACLE(mysql etc..).

SELECT distinct ItemType,
CASE  
    WHEN price between 0 and 499 THEN 'Cheap'
    WHEN price between 500 and 1999 THEN 'Affordable'
    WHEN price between 2000 and 4999 THEN 'Expensive'
    WHEN price >=5000 THEN 'Very Expensive'
END AS classification 
FROM Item order by ItemType,classification asc

edited Jun 18 '19 at 14:31

answered Jun 18 '19 at 14:22

yeshwantreddy kesari

1
1

score 0 · Answer 5 · answered Dec 25 '19 at 06:26

CHECK IT

SELECT DISTINCT ItemType,CASE
WHEN Price BETWEEN 0 AND 499 THEN 'Cheap' 
WHEN Price BETWEEN 500 AND 1999 THEN 'Affordable'
WHEN Price BETWEEN 2000 AND 4999 THEN 'Expensive'
WHEN Price>=5000 THEN 'Very Expensive'
END Classification FROM ITEM WHERE (PRICE BETWEEN 0 AND 499) OR (PRICE BETWEEN 500 AND 
1999) OR (PRICE BETWEEN 2000 AND 4999) OR (PRICE>5000) GROUP BY ITEMTYPE,PRICE ORDER BY 
ITEMTYPE, CLASSIFICATION;

deepthi · Answer 6 · 2019-05-03T12:15:31.440

-1

SELECT distinct ItemType,
CASE 
WHEN
  Price BETWEEN 0 AND 499 THEN 'Cheap'
WHEN
  Price BETWEEN 500 AND 1999 THEN 'Affordable'
WHEN
  Price BETWEEN 2000 AND 4000 THEN 'Expensive'
else 'Very Expensive'
END Classification
FROM Item
ORDER BY ItemType, Classification

edited May 03 '19 at 12:15

answered May 03 '19 at 12:10

deepthi

1
2

5

Welcome to StackOverflow! Please elaborate what this snippet does and how it helps to solve the problem. – quinz May 03 '19 at 12:19
This code snippet will going to classify the item as cheap,affordable,expensive and very expensive. create one more column named classification and displays unique rows sorted by itemtype and classification in ascending order. – deepthi May 04 '19 at 13:28

Vijay Ashok Patil · Answer 7 · 2022-07-23T12:45:54.443

    SELECT DISTINCT itemtype, 
CASE
      WHEN price BETWEEN 0 and 499 THEN 'Cheap'
      WHEN price BETWEEN 500 and 1999 THEN 'Affordable'
      WHEN price BETWEEN 2000 and 4999 THEN 'Expensive'
      WHEN price>=5000 THEN 'Very Expensive'
END  as classification 
  from item  order by itemtype, classification

He ask to display the unique value of item type and classification so I selected it with the help of Distinct.
Then given some conditions to create a classification column, so to create a classification column I used a CASE statement to satisfy all the conditions.
In the last he asked to display both the columns in ascending order so I used ORDER BY.

I hope it will help you.

Need to resolve the query

7 Answers7