Welcome back. As we continue our discussion on data manipulation, and transition to data analysis, we're finally going to get to one of my favorite topics in SQL, case statements. When you're conducting data science, you're always needing to transform variables or recode the data in order to help you with your analysis. As you know by now, most of our time is spent cleaning up this data. Case statements are really easy to start to do this. We use them a lot when we're doing things like when we're hiding, coding, or taking an individual categorical variable and creating its own column out of a binary variable. We also do it when we want to create different groupings. So a lot of times, when we're doing some type of forecasting or predicting, we want to create bins of what we're going to predict. Case statements can help with all of these tasks, which is why they're great to cover and we're going to do that in this video. After this lesson, you should be able to define what a case statement does, describe some situations in which a case statement is useful, explain what each part of the case statement syntax does, use a case statement using appropriate syntax, and explain how to categorize, or bin, your data. Let's begin. The case statement is a built-in function that mimics an if-then-else kind of statement found in most programming languages. This can be used in select, insert, update, and delete statements. The logic that the case statement takes is as follows. You're going to say, case, and then, when, and then you'll have a condition. Then after you say the condition, you put, then, and what the result expression will be. So you could have multiple cases when it's a certain expression and what you want the result to be, and that could go on for a while. When you're done with that, if the expression declared in the when part of the statement is not one of those cases, then at your option, you can elect to say what else the result would be and then you end your expression. This shows just an outline of what it would look like. Let's look at a simple case statement as an example. Let's say I want to reclassify my cities, and I'm interested in looking at just the Calgary cities. And so I want to create a new column, that's Calgary, or Other, for my city. To do this, I'm going to say, CASE City, and then, WHEN it's Calgary, and I have that in my quotation because it's a string, then I'm going to classify it as Calgary. Also, I'll classify it as Other, then I'm going to end this, and I'm going to call the calculation, Calgary. Here, I also pulled in some additional information about the employee, the first name, the last name, their ID, and then I have the city. I pulled in the city so you could see if it's classified right. And as you can see here, that's true. And Lethbridge is classified as Other. And then you can see Calgary, there is a classification as Calgary. This is just a quick way that now, I create my own binary variable for a categorical variable. Instead of calling it Calgary, I could have also named it as zero or one and this would been really great method for a lot of the algorithms that we use, especially in clustering, where you don't want to use categorical variables, so you transform that into the one hot encoding. The only thing I would do differently here if I wanted to do that would be, instead of calling it Calgary or Other, I would call it Calgary with one, and then Other would be zero. You can also use this as a search, though. For this example, I'm going to show you how you could add a couple of the cases together. Here, what I'm looking at in the Chinook database is how I'm going to classify my tracks. I want to classify them based on the number of bytes they have. Again, we have discussed a little bit earlier in the course how we do this when we're doing predictive modeling or forecasting. So here, we may want to bin all of our small sales customers into one and predict their future sales, or large scale customers into another, and so on and so forth. In this case, I'm going to be looking at the size of the tracks, and so I want to bin the bytes. Again, it starts the same way. I say, case, I'm treating it just as I'm selecting another column. So you can see, I have my select statement, I have my selected track ID, name, bytes, and then I say I want it to select the case. It starts, WHEN Bytes, but this time, I have an expression so WHEN Bytes are less than 300,000, then I'm going to classify this as a small. When the bytes are greater than or equal to 300,001 and less than 500,000, then it's going to be classified as medium. And then I have my large category, which would be anything greater than or equal to 500,001 bytes. I also put on here the else, and I put that for Other. You could leave this blank and it will just classify back as a null. But this was just in case there wasn't something I caught. However, the way we've written this should have caught everything. You can then see that in the table, I filled in the blanks and you can see how it's starting to classify this. So, this is just a really nice way to start to create bin groupings for my different statements. Another example of what you can do, instead of saying when it's greater than or less than something, is calculate another field. So for example, let's say you have two different periods and you want to classify and count the cells by those period. You could say when the period is between one and five, then the net sales, else zero. This is creating a condition that is based on, and you can classify, sales during that period. So just something to remember is that the THEN doesn't always have to be a word or a number. It can be a field from another column. All right. So, I just took you through a few examples of what you can do with the case statement. Again, this is something you really want to learn so you can understand all of the uses for them. Honestly, it'll probably surprise you how often you'll use case statements in your work, and what you're able to do with them. So I definitely recommend extending what you've learned here by reading up on the resources and articles regarding case statements. Then, be sure to practice and start using them, and explore all you can accomplish with case statements.
