Using CASE

Contact Us or call 1-877-932-8228
Using CASE

Using CASE

CASE functions contain one or more WHEN clauses as shown below.

Syntax

-- OPTION 1
SELECT CASE column
			WHEN VALUE THEN RETURN_VALUE
			WHEN VALUE THEN RETURN_VALUE
			WHEN VALUE THEN RETURN_VALUE
			WHEN VALUE THEN RETURN_VALUE
			ELSE RETURN_VALUE
		END
	AS ColumnName
FROM table

-- OPTION 2
SELECT CASE
			WHEN EXPRESSION THEN RETURN_VALUE
			WHEN EXPRESSION THEN RETURN_VALUE
			WHEN EXPRESSION THEN RETURN_VALUE
			WHEN EXPRESSION THEN RETURN_VALUE
			ELSE RETURN_VALUE
		END
	AS ColumnName
FROM table

Code Sample:

Case/Demos/Case.sql
/*
Create a report showing the customer ID and company name,
employee id, firstname and lastname, and the order id
and a conditional column called "Shipped" that displays "On Time"
if the order was shipped on time and "Late" if the order was shipped late.
*/

SELECT c.CustomerID, c.CompanyName, e.EmployeeID, e.FirstName, e.LastName, OrderID,
	(CASE
		WHEN ShippedDate < RequiredDate
			THEN 'On Time'
			ELSE 'Late'
			END) AS Shipped
FROM Orders o
	JOIN Employees e ON (e.EmployeeID = o.EmployeeID)
	JOIN Customers c ON (c.CustomerID = o.CustomerID)
ORDER BY Shipped;

The above SELECT statement will return the following results (not all rows shown).Case Shipments Results

Code Sample:

Case/Demos/Case-GroupBy.sql
/*
Create a report showing the employee firstname and lastname,
a "NumOrders" column with a count of the orders taken, and a
conditional column called "Shipped" that displays "On Time" if
the order shipped on time and "Late" if the order shipped late.
Group records by employee firstname and lastname and then by the
"Shipped" status. Order by employee lastname, then by firstname,
and then descending by number of orders.
*/

SELECT e.FirstName, e.LastName, COUNT(o.OrderID) As NumOrders,
	(CASE
		WHEN o.ShippedDate < o.RequiredDate
			THEN 'On Time'
			ELSE 'Late'
			END)
		AS Shipped
FROM Orders o
	JOIN Employees e ON (e.EmployeeID = o.EmployeeID)
GROUP BY e.FirstName, e.LastName,
	(CASE
		WHEN o.ShippedDate < o.RequiredDate
			THEN 'On Time'
			ELSE 'Late'
			END)
ORDER BY e.LastName, e.FirstName, NumOrders DESC;

The above SELECT statement will return the following results.Case Shipments By Employee Results

Notice how the GROUP BY clause contains the same CASE statement that is in the SELECT clause. This is required because all non-aggregate columns in the SELECT clause must also be in the GROUP BY clause and the GROUP BY clause cannot contain aliases defined in the SELECT clause.

Next