Skip to content
Advertisement

Select row with a new column for group

In the table Products in the link below, I have 3 columns:

SupplierID, CategoryID, Price

https://www.w3schools.com/sql/trysql.asp?filename=trysql_drop_table

I want to add column “group” to the output of the select statement.

select * 
from Products 
where Price < 50

Column group has value using below rule:

IF SupplierID < 5 AND CategoryID < 10 THEN group = 1

IF 5 <= SupplierID < 10 AND CategoryID < 10 THEN group = 2 

ELSE group = 3

Thank you for your help.

HHC

Advertisement

Answer

Use case:

select p.*,
       (case when SupplierID < 5 and CategoriID < 10
             then 1
             when 5 <= SupplierID and SupplierID < 10 and CategoriID < 10
             then 2
             else 3
        end) as groupid
from products p;

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement