IF THEN logic with CASE - SQL Tutorial

- [Instructor] The managerial team at WSDA Music has been greatly benefiting from the reports that you have supplied to them thus far. In fact, so much so they're now in a position to set some new sales goals. The managerial team now wants as many customers as possible to spend between 7 and $15 when purchasing music from their online store. As a result, they've created the following categories; a baseline purchase, which is anything that falls between 99 cent and 1.99, a low purchase which falls between $2 and 6.99, the target purchase, which is the sales goal of $7 and $15 and a top performer category which is anything that is above $15. Now, based on these categories, the WSDA Music Sales Department wants to see if there's any information that they can decipher or glean from the database concerning the sales in all of the listed categories. Now this is a great scenario to introduce the CASE statement which will help us perform this level of analysis. All right, we have our basic SQL statement in front of us and all this is doing is searching our invoice table and displaying these fields. Now, we would also like to include the CASE statement, which actually allows us to create a new temporary field in our database that serves as a label for the data based on user-specified conditions. And this is exactly a great case for these user-specified categories that WSDA management has laid out for us. So let's see how we would implement this using SQL. As a first step, we would need to include a comma after our last field, and we include the keyword CASE. Now in a new line, we want to say the first condition and the keyword WHEN total is less than $2 then we want to label this as baseline purchase. Now let's pause and take a look at this. All we have done from the comma here is just included another field so far and we're in the midst of building our CASE statement. So we've started with the keyword CASE and the first condition or category we'd like to include is WHEN the total is less than $2 which satisfies this criteria here which is anything that falls between 99 cent and 1.99. So anything less than $2, we can safely put into this category, which is the baseline purchase. Okay, now let's go to another bucket of information. To do that, let's include another keyword WHEN so when the total is between $2 and 6.99, then this bucket of category is going to be called low purchase. And I'm just going to grab it over here and pop it in here. So let's take a look at this criteria here. When the total is between the value $2 and 6.99, which is exactly the low purchase, we would like to call this bucket low purchase. All right, in very much the same way, we're going to repeat the same syntax. WHEN the total is between 7 and $15, then this becomes our target. That's the target purchase that our sales team at WSDA Music has set for the sales team. Just one more bucket here that we'd like to include, and because if anything is above our target purchase, that means every other combination, we can say ELSE. Let's just call anything else outside of the categories we've specified top performer. Okay, one final thing that we must do to end off our CASE statement is the keyword END. And we can also label this entire field as we say as a purchase type. Okay, now let's take a look at the entire statement here. What have we done? I'm going to highlight the CASE portion and this is simply a calculated field and the CASE statement really allows you a lot of flexibility by doing this. We've said CASE to indicate that there's a CASE statement. Now, when this particular condition is met, when the total is less than two, we want to label this as baseline purchase. Second condition, when the total is between $2 and 6.99, then we want to label this as low purchase. When the total is between 7 and 15, then this is our target purchase, and everything else that's not in any of the previous categories, we can label that as a top performer. Last but not least, we call an end to our CASE statement with the keyword END. And just as we were aliasing any other column, we're calling this CASE statement column a purchase type. Now let's run our statement and observe the result. We have a brand new column that has been created, and they bucket each one of those categories according to what we have specified. So we have top performer, which is anything above 15 and we tell, we have this here. The target purchase which is between 7 and 15. We have $8.91, and that is pretty accurate. We also have a baseline purchase of $1.98, which does satisfy this criteria. And then, we have a low purchase, which is between that $2 and $6 range, and we see that we have this being satisfied as well. And that applies to every single record in our database. Now with the inclusion of the CASE statement, we now enabling management to perform even deeper analysis. So as mentioned before, if our managerial team now wishes to see which cities do their top-performing sales come from, they can actually perform this type of analysis by including even more of a refinement to our SQL statement. So let's see how we can respond to this question by including the WHERE clause in our current SQL statement. The WHERE clause comes after the FROM and before the ORDER BY. So we want to say WHERE, and we can actually filter using our calculated field or our CASE statement, which we've labeled as purchase type. So we can now say WHERE Purchase Type, and I'll just keep it "apples to apples" with capitalizing purchase where purchase type is equal to and we are interested in the top performers. Now again, looking at our total record count, which is 413, let's now run our altered statement with the WHERE clause included, which is actually filtering on our calculated column or our CASE statement, and let's look at the result. We've gone to 12 rows, and if we take a look at the purchase type column in our result, they're filtered to only the top performers. And now, management can see if they take a look at the billing city in response to their question which city accounts for our top performers. Most of the cities that are accounting for the top performers seem to be outside of the United States. And with the inclusion of something like this with regard to the WHERE clause, we are now effectively performing very deep levels of data analysis and enabling the management team at WSDA Music with some powerful new tools to add to their ability to generate sales.