Hey guys, in this video, we will start looking at our aggregate function starting with the average function. So to take an average of the rules are of values in rosewood need some numerical values that would not be IDs. So for this example, and well, we would probably eventually have to add it anyway, we will be adding a column to our enrollments table for grades. So we would be adding grades the enrollments table because this is where we see that a particular student took a particular class. So the best place to put the grid for the students performance in said class would be in the same table where we know that they took that class. So I'm going to modify the enrollments table put in our grades column, and put in some dummy data and then we can proceed.

So to add an add a column to an existing table, we just hover over that tables name In the design viewer, click that arrange in the middle, which will bring up or designer and then we can just add that new column. Alright, so in adding this new column, I am calling it greed. I'm setting it as a float. And I'm setting it as nullable. So sometimes you when you're adding a rule, a new column, sorry, after having a few records, if you are not careful with setting invert null versus not nullable, you might run into some design flaw and problem because if it's not nullable, and you already have 100 Records, then you're going to be attempting to insert a new column that shouldn't be no against 100 existing columns with no preset data. So you want to be very careful with that.

Maybe you want to start off as null, fill in the the records retroactivity and then make it not no going forward. So far, no, I just said it's to be knowledgeable and Having me that change, I'll just click Apply. And then it will run an ALTER TABLE statement, which is the SQL statement that is used to modify a table like when you're adding a column, or you're adding a key retroactively or doing some operation that is morphing, the, the structure of your table. So I just go ahead and click Apply. And that was successful. And I'll just go back to my design viewer and execute once again.

And then I'll just add a few grids here. Alright, so we've added some grades. And having done all that I just click apply and allow it to run that update command setting the grids where necessary, and that was successful. And now we have some data against which we can do a few computations. Now we have our task ahead of us we were asked to bring by the average grades per course. No once again presses elimination, we know that we need courses.

So I can select. Well actually, let's start with enrollments because it's average grade per course. So, enrollments is what really has the grades. So let's start with enrollments select. And I'll say start from enrollments. Alright.

And let me just add my use school underscore dB. So that is our first statement. And we can execute that. And we see that we're bringing back all enrollments. Now we wanted to know, the average grade. For course, based on that objective, I really don't need the course Well, I need the course name because you're seeing of class ID here.

I don't know what course is associated with the class ID five. So that means I'm going to have to inner join my glasses table. So I just read that quickly. And now Start using my NSS. So em for enrollments and seal for classes, and then co.id must be equal to n dot class underscore ID. And when I execute this, then I bring about more details more numbers, though.

But I'm really interested in maybe the course name. So I'm going to have to see, I want, whichever, whichever course has the ID of five. Let me let me take a second to look at these IDs because this is no confusing. But you see here where we have ID and we have ID. All right, yeah. So we're back on track.

So our courses ID, here are our classes ID here is being energized on the class ID here. So that's why we have 5555555 and six on one, but then we're really interested in the courses because we will Want to see the name of the course, as well as the average grid. So I'm just going to duplicate this line and change this out to courses. And I'm going to call this one SEO, and I'm going to see seo.id. Or Well, that would be course ID, and there is the IntelliSense helping me along. So we just do that quickly.

So that's course ID being equal to c l dot courses ID. All right, and then once we do that, then we can bring back more details. And we see here internet authoring is a very popular course this term, and we have website development and data mining. Now the objective once again, is to bring about the average grades per course, which means that I want to see one rule seeing internet authoring and the average grid for that course. And I want to see one rule with website development and wonder with the demand grid Did we only have one row of those right now. But essentially, we shouldn't be seeing this many instances of internet authoring by the end of this query.

So now I just focus on all of the columns that I know I want back. So I want back the course title, which in this case, would be SEO dots title. And I also want back and I just space those out so we can see exactly what's happening here. And I also want back my grade, which would be n dot agreed. All right, and then when I select once again, now I'm seeing that these are the grants for internet authoring and other courses. But once again, this should all be one value, which would represent the average grade for that class.

And well, the average would this would be 87, on average for this will be 34. Now if you're a c++ developer or any other type of developer, you would probably go into a for loop or a while loop mode, where you'd rather statement for this board in SQL, they gave us a command because you can see where it's not quite like the other languages. So we actually have the opportunity to use a function a VG and wrap that grade value inside a VG and a VG will say, I want to average for whatever comes in here. So when we take a look at the results set, we see that we're going to get about one course with some average value, but we had three courses, and we should have at least three rules. And the reason for this is that the Photoshop ABG is not functioning properly because we are not grouping by so I'm going to add a group by and once again, we will no group by whatever we're selecting, but in this case, because we're also selecting a value that we're putting in the aggregate function ABG we won't include this one.

In our group by, so when we're using one of these functions, we don't include that column in our group by. And then if I execute this once again, then we see better values coming back where we see internet offering with its real, average, and website development with those values. Of course, if we add more values for website development and data mining, then we would get different values than the one greed that is available to us. But otherwise, that is how our ABG function works. As always, we can beautify our columns by adding our aliases and we can see course, title, and course average or average grade. And then we re execute and there we have our report, which we can then export and share with our superiors.

Thanks for viewing and have fun with that.