SQL Tutorial - Nesting CASE statements

Learn how to nest CASE statements in this SQL tutorial, nesting CASE statements can be used for layers of conditional logic but can become complex and difficult to read so always remember to add comments to your code, I also like to indent my case statements to improve readability. The other issue with nested CASE statements is that they are not very dynamic but we could overcome that problem by storing the values in a separate table then joining to that table.

To follow along with this SQL tutorial run the below code, also includes nested case statements created in the video, replace greater than and less than with actual symbols:

CREATE TABLE dbo.Customers
(
CustomerKey INT IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_Customers_CustomerKey PRIMARY KEY (CustomerKey),
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
BusinessName VARCHAR(50) NULL,
CustomerType CHAR(1)
);

INSERT INTO dbo.Customers (FirstName, LastName, BusinessName, CustomerType)
VALUES
('Albert', 'Gunner', NULL, 'P'),
(NULL, NULL, 'Beach Store', 'B'),
('Catherine', 'Smith', NULL, 'P'),
(NULL, NULL, 'Duncan''s Hair', 'B'),
('Erin', 'Fairclough', NULL, 'P'),
(NULL, NULL, 'Gaming Zone', 'B'),
('Henry', 'Long', NULL, 'P');

CREATE TABLE dbo.Orders
(
OrderKey INT IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_Orders_OrderKey PRIMARY KEY (OrderKey),
CustomerKey INT NULL,
OrderDate DATE NULL,
OrderAmount DECIMAL(8, 2)
);

INSERT INTO dbo.Orders (CustomerKey, OrderDate, OrderAmount)
VALUES
(1, '20220501', 1000.00),
(1, '20220602', 9500.00),
(2, '20220501', 3000.00),
(2, '20220602', 3000.00),
(3, '20220501', 12000.00),
(3, '20220602', 6000.00),
(3, '20220501', 4000.00),
(4, '20220602', 7000.00),
(4, '20220501', 9000.00),
(4, '20220602', 10000.00),
(4, '20220501', 6000.00),
(5, '20220602', 8000.00),
(5, '20220501', 8000.00),
(6, '20220602', 22000.00),
(7, '20220501', 3000.00),
(7, '20220602', 2000.00);

SELECT
*,
CASE
WHEN CustomerType = 'P' THEN
CASE
WHEN NoOfOrders (greater than or equal to) 3 THEN
CASE WHEN TotalAmount (greater than or equal to) 20000.00 THEN 20.00 ELSE 15.00 END
WHEN NoOfOrders (greater than or equal to) 2 THEN
CASE WHEN TotalAmount (greater than or equal to) 10000.00 THEN 18.00 ELSE 12.00 END
ELSE 5.00 END
WHEN CustomerType = 'B' THEN
CASE
WHEN NoOfOrders (greater than or equal to) 3 THEN
CASE WHEN TotalAmount (greater than or equal to) 30000.00 THEN 25.00 ELSE 15.00 END
WHEN NoOfOrders (greater than or equal to) 2 THEN
CASE WHEN TotalAmount (greater than or equal to) 20000.00 THEN 19.00 ELSE 13.00 END
ELSE 6.00 END
END AS Discount
FROM dbo.Customers AS Cust
INNER JOIN
(
SELECT
CustomerKey,
COUNT(OrderKey) AS NoOfOrders,
SUM(OrderAmount) AS TotalAmount
FROM dbo.Orders
GROUP BY
CustomerKey
) AS Ord
ON Cust.CustomerKey = Ord.CustomerKey;

[BeardedDevData]

Can you think of a more elegant solution?

[oraclesql]

Well done on hitting 1 million total views Bearded Dev. Your channel is pure gold and I wish you were still putting up content

[yaseersayeed]

Hi solution and explanation are awesome, need more case with respect to system time concat statement and many more thanks in advance but it's required for further improvement in this case statement.

[kummithavenkatareddy]

Why we need to give columns in Group by clause which are using case statements?
Select
c1,
c2='x' sum(values in c3) sum(values in c4) end as 'z'
from table A
where c5='Y' and c6='w'
group by c1, ( Do we need to use c2 here )


c1, Z column (I need Z values grouped by only c1 column )

[MethodOverRide]

On mobile so I cannot elaborate, but this feels like it could benefit from a stored procedure to calculate the discount. I'm thinking about calling a proc in the case statement and feed it the parameters for the different discounts.

WHEN CustomerType
THEN EXEC dbo.CalculateDiscount(...)
END AS Discount

[NAVINSUTTLE]

good video, but not able to clearly hear.

[uttambongarde]

In my case statement they provide the conditions for the columns but we need to fetch the values from that column.
E.g. CASE When ABC.(Tbl n).Contact desc= 'phonenumber'.
I want here phonenumbers for different IDs.
How to do this??

[srisanthoshk]

Hi, in all your videos you talk about relational set which is basis for relational databases. So in order completely understand the relational set which math should I learn, so that I completely understand the logic behind any given SQL query? Please advice.