Highest values among corresponding field in oracle

Question

I have two supplier_type (Direct and Indirect) for one supply code. I want to produce a new column supplier_type based on supplier_Name for one supply_code like

Example:

• If supply_Name is Apple,Dell,Hp,Lenovo then supply_type as Direct
• If supply_Name is laptops,mouse,keyboard then indirect for the supplier_code 2023.

Here I want to get the highest value of supplier_type by counting how many supplier_type are Direct and indirect for each supplier_code.

Sample table:

S_CODE  S_NAME  S_TYPE  
----------------------
2023.   APPLE.  DIRECT
2023.   DELL.   DIRECT
2023.   MOUSE.  INDIRECT
2023.   LENOVO. DIRECT
2022.   DELL.   DIRECT
2022.   LAPTOP. INDIRECT
2022.   MOUSE.  INDIRECT

Here, we have supplier code 2023 and 2022

For supplier code 2023 we have 3 direct and 1 indirect, so we need to pass the value as Direct in supplier_type column

And for supplier code 2022 we have 1 direct and 2 indirect, so we need to pass the value as Indirect in supplier_type column.

select supplier_type 
from supplier_table 
where count_of_supplier in (select distinct max(count_of_supplier) 
                            from supplier table 
                            group by supplier_type) 
order by supplier_code