How to use sub-query in SQL to display data in segments

 We are given a table (fruit_imports) which shows us details of imports of different fruits from various states in USA.

Our task is to segregate fruits based on supply availability as per below criteria.

If supply is below 20000 then mark it as Short supply fruit

If supply is between 20000 and 50000 then mark it as Regular supply fruit

If supply is over 50000 then mark it as Excessive supply fruit

This will help the concerned department to decide on the price of fruits as well as arrange to place more orders for fruits that are in low supply.

We are looking for end result in 3 columns...

Fruit : names of all fruits

Total Supply : From the given table, get total supply of each fruit

Supply Category : SHORT , REGULAR ,EXCESSIVE

This is a very common scenario in many situations. Same query logic can be applied in all such situations.

For example, sale of certain item in segments, segments of customers based on order frequency, creating segments of inventory based on fast-low-regular movement, create groups of days in a year based on hot - average - cold temperature...the list goes on and on...

Since all column names we require are to be included in SELECT statement, that would be a good start

SELECT name AS fruit, SUM(supply) AS total_supply

and for categorization, we can use CASE statement as third column. 

We are going to try one category first (SHORT SUPPLY) and see if results are as per our expectation and then repeat the same process for other two categories

SELECT name AS fruit, SUM(supply) AS total_supply, CASE

WHEN SUM(supply) < 20000 THEN 'SHORT'

END 

AS supply_category

FROM fruit_imports

GROUP BY name

We can verify that when supply is less than 20000 then category is printed as SHORT. This looks good so we can now repeat the process for other two categories and complete the result.

That's how we get out result. Sometimes, if the supply numbers are dynamic and can change, then instead of calculating SUM(supply) in the main SELECT query, we can also do it in a subquery under FORM statement

Here is an alternative way...

Note that in the main SELECT query, we are using aliases for name and SUM(supply) that we used in sub-query under FORM statement.

Getting the same result either way.