Case

---

CASE can be used to create categories or group data based on specific conditions. This is valuable when dealing with numerical or textual data that needs to be segmented into different groups or categories for analysis, reporting, or visualization purposes.

Here is a database OrderDetails we’ll use:

OrderDetailID	OrderID	ProductID	Quantity
1	10248	11	12
2	10248	42	10
3	10248	72	5
4	10249	14	9
5	10249	51	40

Basic

SELECT OrderID, Quantity,
        CASE 
           WHEN Quantity > 30 THEN 'The quantity is greater than 30'
           WHEN Quantity = 30 THEN 'The quantity is 30'
           ELSE 'The quantity is under 30'
        END AS QuantityText
FROM OrderDetails;

Output (partial):

Order ID	Quantity	QuantityText
10254	21	The quantity is under 30
10255	20	The quantity is under 30
10255	35	The quantity is greater than 30
10255	25	The quantity is under 30
10255	30	The quantity is 30

Basic As

• You can always save the results of the CASE query in an ALIAS
• Here we’ll clean the data and save in a temporary object: cleaned_name

SELECT
    Customer_id,
CASE
    WHEN first_name ='Tnoy' THEN'Tony'
    WHEN first_name = 'Tango' THEN 'Taco'
    ELSE first_name
    END AS cleaned_name

Order Unless

• The following will ORDER BY the customers by City
• In the CASE City is NULL then it will ORDER BY Country
• Use Customers table

CustomerName	City	Country
Drachenblut Delikatessend	Aachen	Germany
Rattlesnake Canyon Grocery	Albuquerque	USA
Old World Delicatessen	Anchorage	USA
Vaffeljernet	Århus	Denmark
Galería del gastrónomo	Barcelona	Spain

SELECT CustomerName, City, Country
FROM Customers
ORDER BY (CASE
                WHEN City is NULL THEN Country
                ELSE City
         END);