SQL CASE Statement

The CASE statement in SQL is used to evaluate a series of conditions and returns the values when the first condition is met. It is very similar to if-else statements. As the first condition came out to be TRUE, it will stop the process and will ignore the other conditions. If no part of the IF condition is true then it will get parsed to the ELSE condition and returns the value associated with it.

If no conditions are TRUE, then it will return NULL as its result.

  • Syntax of CASE Statement –

CASE
WHEN [Condition1] THEN [Result Expression1]
WHEN [Condition2] THEN [Result Expression2]
WHEN [Condition3] THEN [Result Expression3]
.....
WHEN [ConditionN] THEN [Result ExpressionN]
ELSE [Result Expression] END;

Demo Table – 

Product Table

Category Region City Country Customer_ID Customer_name Sales Profit
Office Supplies Central Chicago United States SM-20950 Suzanne McNair 15.696 5.1012
Technology East New York City United States AH-10465 Amy Hunt 539.97 134.992
Office Supplies East New York City United States AH-10465 Amy Hunt 22.58 5.8708
Office Supplies East Dover United States EP-13915 Emily Phan 16.52 7.5992
Office Supplies East Dover United States EP-13915 Emily Phan 671.94 315.8118
Technology West Aurora United States TP-21565 Tracy Poddar 169.064 -14.7931
Office Supplies West Aurora United States TP-21565 Tracy Poddar 168.624 14.7546
Office Supplies East Long Beach United States AR-10825 Anthony Rawles 19.44 9.3312
Office Supplies West Pasadena United States HA-14920 Helen Andreada 31.92 8.2992
Furniture West Pasadena United States HA-14920 Helen Andreada 433.568 -65.0352

Example – Consider the Product table of the Customer database, and show the profit status as Profit or Loss. When the profit is greater than ‘0’ call it Profit and when the Profit is lesser than ‘0’ replace it with Loss.


SELECT Category,Region,City,
[Country/Region],[Customer ID],[Customer Name],Sales,Profit,
CASE WHEN [Profit]>0 THEN 'Profit'
WHEN [Profit]< 0 THEN 'Loss'
ELSE 'No Loss No Profit'
END AS Status
FROM [dbo].[Products];

The output shows the execution of the CASE Statement, and also has an alias for the Case statement as Status –

Category Region City Country Customer_ID Customer_name Sales Profit Status       
Office Supplies Central Chicago United States SM-20950 Suzanne McNair 15.696 5.1012 Profit
Technology East New York City United States AH-10465 Amy Hunt 539.97 134.9925 Profit
Office Supplies East New York City United States AH-10465 Amy Hunt 22.58 5.8708 Profit
Office Supplies East Dover United States EP-13915 Emily Phan 16.52 7.5992 Profit
Office Supplies East Dover United States EP-13915 Emily Phan 671.94 315.8118 Profit
Technology West Aurora United States TP-21565 Tracy Poddar 169.064 -14.7931 Loss
Office Supplies West Aurora United States TP-21565 Tracy Poddar 168.624 14.7546 Profit
Office Supplies East Long Beach United States AR-10825 Anthony Rawles 19.44 9.3312 Profit
Office Supplies West Pasadena United States HA-14920 Helen Andreada 31.92 8.2992 Profit
Furniture West Pasadena United States HA-14920 Helen Andreada 433.568 -65.0352 Loss

 

Other SQL topics to check out:

Follow us on TwitterFacebookLinkedin, and Tableau Public to stay updated with our latest blog and what’s new in Tableau.

Automate data analysis pipeline and create report ready dashboards

If you are looking forward to getting your data pipeline built and setting up the dashboard for business intelligence, book a call now from here.