Hey guys. So we're continuing our exploration of aggregate functions in SQL, and today we'll be looking at columns. Now our scenario is to bring back the number of students enrolled per course. So we can just start up our query, usually where we select star from our enrollments table. And we look at that data set. And what we want.
What we're interested in is the number of students per course. So we know we need the course to be filtered out based on its ID. And then we need to count the number of students that match each course. So that means course one should have 123 students, and the same logic would apply for every other course. So firstly, process of elimination, what I really need for this scenario, I need a course ID and I need this Student ID. And when I do that, we only bring back those two values.
And by now, we should all be experts on joins and inner join, some would not want to focus on inner joining and making this beautiful, I just want us to focus on the function that is the aggregate functions. So the next thing is that we want to use an aggregate function to columns, the number of students, which means that for chorus, one, I should only see one and three. And even though you see that here, when we finish the query, I shouldn't be seeing one and three, one and six, one and 12. But I should just be seeing one. One rule with course ID one on a count of three. And the same would apply for every other course ID that is present with the number of students present.
So I can just use my aggregate function. And then I will get an arrow and I get this arrow because it's it's telling us that we have to use group by so that's just a warning, you can't use an aggregate function unless you're grouping by. So the next step would be to group by. And remember that we're not grouping by whatever column is being aggregated. So we use group by course ID only. And then when I execute this, I get back course ID one with a count of three, course ID two with a count of four.
So that means that course for each course I'm seeing how many students are enrolled to this course. Alright, so just took a few minutes and modify this course so it looks a bit more beautiful on the printout. So I just included the course code. Well, I included the courses table through an inner join here. And then I included the columns the course code, give it a nice beautiful heading the title and gave our cones portion a nice fitting also. And of course, because the cardinal rule is once your grouping by you have to include their distinct columns, I had to group by the title and the course code.
So because those are repeats the group by clump them together, but also included an count for the number of chomped records. And so that's how we're able to use this aggregate function to get a count. And so down here, you'll see that this is the output. And this looks like something you could actually turn into your administrator when they send a request for such a report. So you can go ahead and play with this the script file is included for further reference and have fun