Hey guys, in this video we will be looking at SQL min and max aggregate functions. In this scenario, we want to bring back the highest grade and the lowest grade for each course. And though the way to do that would be to select everything from enrollments as we know, that's where the grades are being stored, and then grew, and then bring back the min and the max. So we can start off by saying select, and we can say class underscore ID. And then we want the greed from enrollments. All right, and if I do that, to begin, and we need our use statements, changing the scenario, from course to class, all right, so I'm selecting the class ID and the grade from enrollments.
And then we do that select and then we're seeing that each class and we have all of these Grades already looked at all to average them. But in this situation, we only want the highest degree then the lowest grade. So far for class number six number they have one grade. And the same for class number one, but we have our range of grades for class number five, and we should expect that the lowest return would be 39. And the highest return would be 100. It the pirates above the Enter joining and getting the details by no that should be muscle memory to use.
So I'm just going to focus on what the functions are and how they work. So to get any function, any aggregate function working, firstly, we need to group by and then we use the column that won't be involved in the aggregation which in this case is class ID. However, grid is involved in the aggregation. And so what I need to do is wrap this in the function mean, which will then for every record that is grouped find what is the lowest one You. And then I have one for mcse, which does the opposite by finding the highest value for each record that is grouped. And when I execute this, I see I'm getting back only one role or record per class.
And I'm getting about the minimum grade for class number five, which is 39, and the maximum grade up 100. Of course, these two values are the same for the other classes because there is not a wide selection, there's only one grade so the min and max would be the same value in that situation. And that's essentially how min and max work. Of course, you can always just expand this and add your aliases to the columns so that you have more presentable column headers for your reports.