SQL Tutorial - Additive CASE statements

Another video brought to you by BeardedDev, bringing you tutorials on Business Intelligence, SQL Programming and Data Analysis.
If you like the videos you can support me on Patreon, / beardeddev
In this video I talk about how to use additive CASE statements in SQL Server.
For an introduction to CASE statements check out this video: • SQL Tutorial - CASE St...
If you are new to CTEs check out this video: • SQL TUTORIAL - CTEs Pa... , there is also a playlist on my channel
It is important to note that in the first case statement:
CASE WHEN Cat.EnglishProductCategoryName = 'Bikes'
THEN 0.10 END
This will cause any product categories that are not Bikes to be NULL, if this is not the desired result you can edit the query by adding ELSE:
CASE WHEN Cat.EnglishProductCategoryName = 'Bikes'
THEN 0.10 ELSE 0.00 END
CASE Statements were introduced to SQL Server in 2008. They are used to evaluate conditions and return a result based on those conditions. CASE Statements can be used in SELECT, UPDATE, DELETE, WHERE, HAVING.
In this CASE Tutorial we look at a common use of CASE statements within SELECT and also the benefit and common downfalls when writing CASE queries.
CASE Statement Syntax:
CASE WHEN [condition] THEN [value if true] ELSE [value if fales] END
SQL Queries in this video:
SELECT
Cat.EnglishProductCategoryName
, Subcat.EnglishProductSubcategoryName
, Prod.EnglishProductName
, CASE WHEN Cat.EnglishProductCategoryName = 'Bikes'
THEN 0.10 END
+ CASE WHEN Subcat.EnglishProductSubcategoryName = 'Road Bikes'
THEN 0.10 ELSE 0.00 END
+ CASE WHEN Subcat.EnglishProductSubcategoryName = 'Road Bikes'
AND (Prod.EnglishProductName LIKE '%Red%'
OR Prod.EnglishProductName LIKE '%Yellow%')
THEN 0.05 ELSE 0.00 END
AS Discount
FROM dbo.DimProductCategory AS Cat
INNER JOIN dbo.DimProductSubcategory AS Subcat
ON Cat.ProductCategoryKey = Subcat.ProductCategoryKey
INNER JOIN dbo.DimProduct AS Prod
ON Subcat.ProductSubcategoryKey = Prod.ProductSubcategoryKey
WHERE Cat.EnglishProductCategoryName = 'Bikes'
--AND Subcat.EnglishProductSubcategoryName = 'Road Bikes'
Please feel free to post any comments.

[@vikkichang232]

Finally... something that was helpful THANK YOU!

[@johnbreslin4521]

That was really good, didn't know you could do this with CASE statements

[@newbieuserguy6899]

It would help students / neophytes if the 3 discounts were unique values. By using 10%, 10%, and 5%, there's an opportunity to misunderstand which of the two "10%" occurrences is being applied. I suggest using 13%, 10%, and 5%.

[@BeardedDevData]

That's a fair point.

[@gilenomascarenhas6693]

Okay, that was brilliant, didactic and rare. And that goes for both the example and the performance. Thank you, sir!

[@BeardedDevData]

Thanks so much for the positive feedback.

[@TomerBenDavid]

Great video and great speaker!

[@superfreiheit1]

LIke is expensive, you could also use the Color column.

[@padhisambit]

Really nice information...helps a lot !!

[@subhanmehdizade2852]

Thanks, sir. Many many thanks! wish u best.

[@hamiali]

Awesome..Great speaker..!!!

[@chandrarama1970]

You are back !! :)
Your videos helped me a lot in my college assignments. especially the window function series.

[@BeardedDevData]

Thanks so much, if there is anything you would like to see a tutorial on please let me know.

[@chandrarama1970]

@@BeardedDevData Sure!!!!!!!

[@surbhikabra519]

@@BeardedDevData Hi, I loved your window function series too. I would like to see how we could solve questions like 'Earliest order id for each other, each day of the month' How do you dig through different dimensions'?

[@modhua4497]

Excellent example

[@MNSHARIF623466]

helpful

[@fulvioalario6986]

Great video. Thanks. Just a question. Could I use the AND operator instead of + ?

[@BeardedDevData]

Not in this case as we actually want to perform addition.

[@bamii_a]

love the videos mate, where can I download the db ?

[@BeardedDevData]

Thanks so much, you can download the .bak files for Adventure Works from here, docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver15&tabs=tsql, I used AdventureWorksDW2016 in the vido

[@pson3672]

Thanks for your tutorial.
My doubt is can we add one more columns to group all the discounts together.
For example: when the discount is 10% it should be an "Avg discount" mentioned in another column, when discount is 25% it should be "Max discount".

[@BeardedDevData]

If my understanding of your question is correct then you mean instead of
CASE WHEN Cat.EnglishProductCategoryName = 'Bikes'
THEN 0.10 END
we have
CASE WHEN Cat.EnglishProductCategoryName = 'Bikes' THEN AverageDiscount END
(providing AverageDiscount is a column)
then yes we don't have to use literal values within CASE statements, can refer to other columns

[@JD_018]

Great Video. I was wondering, can I use similar case statements to return a text value and not carry out any addition operations? Thanks

[@BeardedDevData]

Sure, for example CASE WHEN Quantity < 10 THEN 'Small' WHEN QUANTITY < 30 THEN 'Medium' ELSE 'Large' END AS OrderSize