Hey guys, today we'll be looking at our SQL count function. Now I have here on screen two scenarios we want to bring by the number of students enrolled per class and the number of students enrolled per course. And we will use these examples to explore how the count function in SQL helps us to accomplish this. Now, the first thing that we want to do is look at who is in the enrollments table so I can select star from enrollments. And as always, I'll use my school dB. And then once I select all from enrollments, I can see the students by ID and what class they are in.

So for the first scenario where we want to see how many students are enrolled per class, it means I want to see how many students are in class number five, class number six and class Number one, so I want up columns. So what I can do here is say group by. And then well we have all the columns, what are the columns, I really want. I want maybe the class ID and the student ID, because I want to know what class and the number of students in third class so I can say class on the score ID. And I will take students on the score ID, and then I will group by whatever it is I'm selecting. And then that will bring back more distinct values, Well, not really distinct values, or just eliminated some columns.

What we want to do is instead of bringing back this menu, we want to bring back the class ID and account which would be 123456 for class number five, and one and one for class number six, and class number one. So you Have a function called count where we can wrap any value around in the codes. And then what this will do is once it is grouped, it will count the number of records being grouped. So when I execute this, oh sorry, execute this without the selection. Then I see Oh, I'm not getting the card thing because I'm grouping by student ID remember that student ID changes each time, so it's still not distinct. So I'm going to take students ID off the GROUP BY clause and remember that once we wrap a column inside of one of these arrogant functions, we don't have to and probably shouldn't be including it in the group by so if I do this again, and we see a better colons where we have one in class one, we have six students in class five, and we have one students enrolled in class number six, then we can expand all of this to see more detail so we can enter join or classes table.

And instead of bringing back the ID, I will bring back the name so that it would be C dot title. But then this is also going to cause a problem because the class, a class really doesn't have any need. The class is only made up of the lecture ID and the courses. So what I would have to do is then INNER JOIN once again, on the courses table, so that I can get the details on the course. Now that looks a little better. And let's just dissect what's happening in our in our joints.

So up until this point, when we're joining, we were joining everything up to the main table that has been selected from in this situation, however, I'm doing what I call a daisy chain, where I'm selecting from one table and then I'm inner joining onto another table, and in the third INNER JOIN, or the second INNER JOIN, or which brings us the third table is actually being joined onto the previous new join table. So let's look at that, again. we're selecting from enrollments, and then we're joining enrollments on two classes. And well, I didn't include the earlier series, so just so we can see exactly what's going on. I'll put on the alias. So, enrollments class ID, most much a class ID in the classes table.

And so this inner join is bringing enrollments and classes together. However, there is no condition on which enrollments can be joined the tour courses. So the only way to get courses in the mix is to then join onto the classes table or the classes records. And so in my inner join for courses I'm joining the course ID found in the course is stable onto the course ID phone in the cloud. is stable. So for ever enrollments we're bringing back our class.

And in for every class, we're bringing buck, of course, the course details. And as a result, we can then access the title of the class, or the course associated with the classes. And so in our results set, we see that we have the name off the course, with six, and then the website development with one and the data mining with one. Now the next scenario that is presented would be bringing by the number of students enrolled per course, though the significance of course versus class would be that. Remember that an instance of a class is based on the classes table course being taught by a lecturer, that's a class but many courses could be taught because in the courses table, we have many courses and then each of them could have different class classes scheduled. All right.

So essentially, for as many enrollments as there would be for classes one and two being which are the same course, then this second scenario should capture that. So if there were three classes available for website development for argument's sake, and there were 20 students in session number 110. In session number two, and 15. In session number three, then we should be able to write a query that shows that for website development as Of course there are currently and my math is going to fill in here. So just work with me. 45 students doing website development that will be different from knowing how many are doing session one versus how many are doing session two.

So this query is bringing by Coleman he would be doing each session so I could include the time Here. So that will be seen dot time. And that would bring about for this class session at five o'clock for internet authoring their six students for this cloud session at that time, there are that many students. However in the second scenario, we would want to see just for internet authoring, regardless of the time how many students are there, so I'm actually going to leave you to put in some sample data and to write that query and see what comes back. I'm going to do the same and upload the solution and he can mix and match. And that's essentially how the colon function works.