Unable to write the query for SQL-CodePudding

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.

CodePudding user response：

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