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

PROBLEM:Classify orders into3 different categories.

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

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

‘Expensive Order’– when the order amount isgreater 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.

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