Hey everyone, welcome back. In this video, part 16 of topic three in our database class, I'm going to talk about how you can use numeric functions in the structured query language. Specifically, we'll talk about the count, min, max, sum, average, and standard deviation functions. Let's get started. So SQL provides us with a variety of built in functions that we can use to retrieve aggregate values. For example, if I want to know how many employees I have, I can use account function. If I want to know what is the smallest salary among any of my employees, I could use the min function to get the minimum salary. If I wanted to use the find of the maximum salary, I could use the max function for that purpose. So as it says here, these things can be used to count the number of rows that match specified criteria, find the minimum value among rows or the maximum value among rows. And it doesn't stop there. We have additional ones as well. So here's some additional aggregate functions that we can use like a sum. So I'm sure you recall from your accounting classes. That is a total. So the total for a specific column for those rows that match whatever criteria we specify and get with a sum. We can use the AVG function to get the average or the mean of a column for rows that match whatever criteria we specify. And we have additional ones as well, like those of you that have studied some statistics, you can do a standard deviation. And of course, you know that if you have an average in a standard deviation, you can describe any normal distribution. So these are all built in aggregate functions that SQL makes available to us. So let's say, for example, that we wanted to find if you remember our database here has an employee skill table. And one of the attributes out there is a skill level. So maybe we want to use that as the basis for illustrating some of these concepts. And all this right, some stuff out here that's in our employee skill table. So let's say that I wanted to find out the minimum employee skill value. And what do I call that? I think it was called skill level. That's right. Yeah, this specify the actual name skill level from employee skill. So if I run this right now, we'll get the smallest value. and see the smallest employee skill is five. Do note that this results set, although it's providing me with the answer that I want. There's no column name. I don't know if I want to provide a name for whatever this value is. I can do that by using an alias and in SQL. We can provide aliases using the keyword as. So maybe I call this something like min skill. I don't know why it's exactly the same when I run it. You will note that the column name down here is now min skill. So it's going to just help me visually understand what that value represents. And similarly, if you want to do a max. So max, you do that. Maybe this is our maximum skill level in the employee skill tables. We run this. You can see the maximum skill is 10. And we can do some other crazy things as well. I don't know. Maybe we want to. Do something like this where we get both of them simultaneously. So here we'll get the minimum skill and the maximum skill. And you can see we can pull those values out of there in max. Some other aggregate functions that we have available to us are, I don't know, if we want to do a count. So like how many records are in the employee skill table. 18. Or if I want to know how many employees I have, I can just count the number of records in the employee table. I have 12 employees. I wonder maybe I want to know how many departments I have. I have four departments. So count can be used for those sorts of things. Let's see. Back this up a bit here. What else do we want to learn this we did count men the max. Let's do an average. This will be an interesting and instructive ones. Say we want to know the average skill level. Now if I run this, we'll see that we get this average skill level of 8.11 111 111. And if I take a look at my employee skill table, it's design. You'll see that my data type. For the skill level attribute is float. So this is a double precision floating point value. It basically means that I can store numbers in here that have a decimal component. Right. So when I take the average of a column that uses like a floating point data type, that supports a decimal component, the result that I get back. Also can have a decimal component. As we see here, the average skill level is 8.11 111. Right. However, this is not the case for attributes, numeric attributes that use an integer data type. So let's say for example, that instead of looking at the skill level. I look at something like the employee ID. Now employee ID has an integer data type. And when I run this, you'll see I do not get any sort of decimal component. And that's because if you try to use like an average function on an integer, the database will perform integer division. I know it may have been a while. Usually people learn this stuff around like seventh grade. So I'll just give you a very brief refresher of the difference between integer division and like decimal division. So if I just do something like this, say 5.0, oops, a decimal 5.0 divided by 2.0, we should know that the result will be 2.5. You can see if I run that I get 2.5000000. Now, because I provided a decimal component to the numbers here, the database is smart enough to know that I want to do decimal division. So I get a result with a decimal component. However, if I provide this with integers for my arithmetic, in this case 5 divided by 2, you'll see that we just get the value of 2 as the output. So it's doing integer division. That is there is a decimal component to the results, but that decimal component is dropped. So it's very, very important that you pay attention to this when you are working with SQL select statements and you're trying to get things like averages or standard deviations. Because we need to know that they're possibly a decimal component there, and if we're doing those operations on integers, then there's the possibility that we will be getting the wrong result. So if I, for example, had used an integer data type to record the skill level of my employees, and I wanted to know the average skill level, I'm not going to get that decimal component. If we ran this, let me replace that with the skill level again, always average skill. So if we run this, we get 8.111, but if skill level were, an integer, our result would be 8. Now, as you sometimes, oftentimes, in fact, decimals really, really, really matters. Just pay attention to that to little detail when you're working with these things. Okay. I think I can leave that for now. What else can we do that I want to show you with these aggregate functions? Oh, let's do something like, we'll limit the results to a specific employee. Okay. So maybe I want to know the average skill level for a specific employee. Oh, of course, can do that by adding a where into here. I'm going to say, I don't know where employee ID equals one. Okay. And this will give me the average skill level for employee number one. If I run this, we'll see it's 7.5. Now, if I would actually look at the data out in our employee skill table, and we looked at employee number one. Let's see. So here's employee number one. And that skill level is, and then we have another one down here, employee number one, and that skill level is five. So the mean, the average is 7.5. So we are getting a correct result out of there. So this is interesting. If we wanted to include like the employee's number in there, we have a couple of ways of doing that. One would be to do something like this. We just put that in there. Select the number one as an employee ID. Calla. And they could run something like this. And that'll give us employee number one and that result. Another approach to accomplishing the same thing would be to use a group by, but we haven't learned that yet. So I just wanted to show you one way of doing this, but for now, I could achieve the same result here. I put it in a group by in. But again, I don't expect you to know how to do this at this point, since we haven't talked about it yet. I just want to illustrate that you get exactly the same results by using that approach.