Unable to write the query for SQL

I am trying to write a complex SQL query. I am almost there but I got stuck.

PROBLEM: Classify orders into 3 different categories.

‘Regular Order’ – when the order amount is less than or equal to 10,000.

‘Not So Expensive Order’ – when the order amount is less than or equal to 60,000 and greater than 10,000.

‘Expensive Order’ – when the order amount is greater than 60,000.

Print the name of the category in which the orders have been
categorized into in the first column followed by count of such orders
in the second column. Sort the result set in descending order of Count
of orders.

Database Schema Image

Query:

select distinct IF(Total_order_amount <= 10000, 'Regular Order', 
       IF(Total_order_amount > 60000, 'Expensive Order','Not So Expensive Order') ) AS 'categories' 
 from Orders;

Output:

categories
0 Not So Expensive Order
1 Not So Expensive Order
2 Not So Expensive Order
3 Not So Expensive Order
4 Not So Expensive Order
5 Not So Expensive Order
6 Expensive Order
7 Not So Expensive Order
8 Not So Expensive Order
9 Regular Order
10 Not So Expensive Order
11 Regular Order
12 Not So Expensive Order
13 Regular Order
14 Not So Expensive Order
15 Not So Expensive Order
16 Not So Expensive Order
17 Not So Expensive Order
18 Not So Expensive Order
19 Regular Order
20 Not So Expensive Order
21 Not So Expensive Order
22 Not So Expensive Order
23 Regular Order
24 Not So Expensive Order
25 Not So Expensive Order
26 Not So Expensive Order
27 Not So Expensive Order
28 Not So Expensive Order
29 Not So Expensive Order
30 Not So Expensive Order
31 Regular Order
32 Regular Order
33 Regular Order
34 Expensive Order
35 Regular Order
36 Not So Expensive Order
37 Not So Expensive Order
38 Not So Expensive Order
39 Not So Expensive Order
40 Not So Expensive Order
41 Regular Order
42 Not So Expensive Order
43 Not So Expensive Order
44 Not So Expensive Order
45 Regular Order
46 Not So Expensive Order
47 Not So Expensive Order
48 Regular Order
49 Not So Expensive Order

I am expecting the count column as well.

>Solution :

So you are missing the count, I think it should work this way:

    select IF(Total_order_amount <= 10000, 'Regular Order', 
       IF(Total_order_amount > 60000, 'Expensive Order','Not So Expensive Order') ) AS 'categories' , count(*) as count
 from Orders group by  IF(Total_order_amount <= 10000, 'Regular Order', 
       IF(Total_order_amount > 60000, 'Expensive Order','Not So Expensive Order') );

Note that the syntax you used for IF is not general SQL, I tried in sqlServer and it is considered incorrect

Leave a Reply