Hey guys, in this video we will be looking at our a VG or average aggregate function. And this is building on top of our newly acquired SQL group by statements use and skill. And in this video because we need average, and averages, numerical and when to make a database structure change. So our enrollments tables originally is designed to have a letter grade. Oh, that's why we started as an HR tool, I'm just going to modify the design so that we have a few number grades and then I'm going to enter some numbers in that table. So to change the design, just as a refresher, we can just right click the table click design.

And the good thing is that we had no data in our grid column. So you can bear this in mind when we can design change to your column. If data is in there, then the data may get corrupted based on the type of change that you're about to make. So moving From a string to let's say, a float is actually a destructive kind of change to any string that was there and you'd probably get an error, at least moving from a floor to a string, or to an entire and var char might not be as disruptive as the other way around. So be very careful when making those design changes. And if you already have grids in there, then you can probably create another table just so you can follow along with the examples for this video.

So I made this design change, I changed grid to floats and I can just click Save. And you may get a warning that says that you are not allowed to save changes. And that's good because these are errors that you may encounter when making database changes. So we're going to go through this error together. So when you encounter that error, you need to go to Tools, Options. And then you're going to look for the designers subcategory and table database designers and then you're going to Antti this option that says prevent saving changes that require table recreation.

So like I said, certain changes can be disruptive. So I'll just untick that, click OK. And then click Save. And then in the background, it will actually execute update scripts, because just in case I made any change that would affect the related tables, then it needs to pull those changes across. So I just click Yes. And there we go. Next, I'm just going to quickly right click, go to Edit top 200 and enter some grid values.

Alright, so I quickly entered some values. And I can assure you that there was no bias with these values. And so now that we have some numerical values that are not IDs, because averaging IDs wouldn't really be of any use to us, we can actually build into our aggregate functions. So I'll just create a quick query here to select Start from enrollments. And then we can build on that. So we have all the enrollments and we have a bunch of grades.

Now let's say we wanted to see the average grade for course ID one. Alright, so whatever course ID one is, we just want to see what was the average grade for that class. So there are a bunch of things that we need to do. So we have all the records from enrollments. And next we need to filter on the particular class. So we're just going to see where and get the course ID equals to one I know we can execute that.

And we see we're only seeing that or three students, or of course ID one and these are the grades once again, we want the average for these grades. Now, if you're a programmer, then you know programmatically to find an average you would use a loop of some sort. And if you get fancy, then maybe a recursive call. But the fact is that you would need some more Repetition structure in place to actually go through, find all the grades, sum them, and then divide them by the number of grades that there were that's a mathematical way and that you can find that algorithmically. No, SQL doesn't necessarily. I mean, it does support a repetition structure in the in the in the form of a loop or cursor.

But in this situation, we don't have to go that far, we can use our aggregate function combined with our group by to accomplish this task. Now, in selecting this data, we really don't need to know the teacher ID or the student ID, because we're only interested in the course that is ID one and the average grade. So we can just narrow down our selection. Remember that process of elimination, and we take only the course ID and the grades. And so we filtered it down to just the course. idea and just agreed.

But then what we really expect to have is one course and just this one record that has the course ID and the grid value. And in this case, the grid value should be the average. So the next thing we need to do is building off of our last video is grouped by so we say group by, and we only have two columns. So we can just take those two columns and group them by that and then we know that if we execute nothing much changes, maybe just the order. So our group by didn't change much, but then this isn't helping because we're still not getting the average. So because we're grouping by and remember that the group by actually keeps track of everything that it is grouping, so that I and I forget function can be employed to do some form of mathematics to some value.

So in this case, the math we want to do is to find the average of This column. And so our function to find the average is a V, G. And like any function in most programming languages, we wrap them, wrap that column in parentheses. And so what this is going to do is when it executes, it's going to group the course ID. So you know, we've seen that the group by is only going to return it's all the trend is returning to return as few rows as possible. So he's going to try and group all of the ones into one. And then because we're running this aggregate function, the ABG is actually just going to say how many rules should I group and perform this match on so when I execute this, we get back one rule with one value.

And no, we did not because I misled you just know. Actually, what should happen is that once you're doing and performing that aggregate function on a certain color You don't buy it. So the group is still going to take into account the fact that they're distinct values here and still bring them back. So if I'm going to be aggregating on that column or running this average, or max or whatever it is kind of function, then I really should not be grouping by that first column. So I will just remove that. And so I'm grouping by force ID, which I know is repeating, and I'm averaging everything that needs that group, that grouping where course ID is equal to one.

And so when I execute, we'll see that we're getting about one rule with course ID one and average and if you're not trusting the math, you can always pull out a calculator and test it yourself. And so if I remove this WHERE clause and execute then it will try to group all the courses by their perspective IDs, and then bring back the average grade for every enrollment into that particular course. So if I wanted the average grades across all courses, then this square would suffice. And so you see that the average grade I cross course one is 70.7 3.3, and four course two, it's a 2.3, etc, etc. And because there are no grades put in for seven, then the average grade is not. And if we wanted to order this by then we could just add in our order by underneath here, and what do you want to order by we want to order by a VG grade.

And there we go. So it's from the smallest varied value to the highest. And if we wanted to eliminate that, no, then we could just over close to see where a VG grade is locked. Below and there you're seeing that it cannot have a where clause with our agreed with our aggregate function. Alright, so that brings me to my second point on aggregate functions where if you want to have a where clause on an aggregate value, then you got us where but you have a keyword that's is having and then you can see a VG grade. And then and there you go.

So whenever you want to filter on a regular column, you know you have your WHERE clause. If you want to filter on an aggregated column, meaning you're employing one of these aggregate functions and remember that you have a few the Moxie have many of so many of columns. And we'll get into all of them as we go along. But once you're filtering on one of these aggregate columns, then you have to use having And having follows the GROUP BY clause, and then you add your condition. So this could be a VG greed is greater than 70. And that would bring back only 70.

And up, it could be is equivalent to 70 equals b is like, you know, whatever it is, of course, we have nothing that is equal to 70. But equity is 70.3 to bring back exactly what that wants. So, anytime you want to filter you have to use your having. And of course, we can beautify the output here by just enter joining all of our columns. And remember the rules once you're grouping by, you have to include every column that is not being aggregated. All right, so you can play around with that.

Chandran queries trying to come up with scenarios. I'll include scenarios at the end of this section that you can use to practice and overall, just have fun