Time
9 hours 41 minutes
Difficulty
Intermediate
CEU/CPE
10

Video Transcription

00:00
I welcome the module. Six. This is less than 6.2 aggregation functions Now. Aggregation functions play a big role in writing advanced queries and sequel.
00:10
They perform calculations on a set of values. They come. They are commonly used with group by statements or having clauses, and we'll take a look at those and the upcoming lesson.
00:22
Most common use involves various types of reports, financial reports, user statistics. Any time you want to show data aggregated together in some way, you'll typically turn to the aggregation functions.
00:36
Something to consider when working with aggregate functions is that they ignore no values. And this is except the count function, which will not ignore the no value now. With that said, Let's go to our development environment and take a look at using some of these aggregate functions.
00:55
I welcome back to the development, Vayrynen said. I decided to take a quick look at the my sequel Documentation. Look at their aggregate group by function descriptions. We could see we have quite a few available. We'll be focusing on the average and the count for this lesson
01:08
Now, with that said, let's go ahead to and head over to our database and take a look at a nd beaver. And let's answer the question of how many students per school do we have
01:19
now. There is a special consideration that we need to take care of because it wasn't accounted for in our database Cedar and we're just going to make do with what we have. So on the user types table, we should have an entry for each school that we have.
01:36
So we have three schools, I believe so. There should be a total of six entries on this table
01:41
to represent teachers and student types at every school. Now, toe just saw this very quickly. We're gonna pretend that this school I d. Isn't even there that we only have these 12345 columns so that the teacher and student type is shared
02:00
between all schools.
02:01
And you do see that concept in databases at times as well. You will see some tables that are shared between all the companies.
02:09
And I said, let's start programming.
02:13
So we want to get the number of students per school. So how would we do that? Well, our user type is indicated on the pivot table So on the pivot table, which is user courses, were indicating
02:28
whether or were indicating what role a user plays someone just go ahead and start this query. So select star
02:35
from
02:36
user courses.
02:38
You wanna alias that with a You see,
02:40
I would drop down to the next line,
02:43
and I'm gonna join user type
02:46
the user types table because that's where the types indicated. I'm gonna alias that with the u t gonna say on you see dot
02:54
use a type I d
02:57
equals ut that i d
03:01
and
03:02
u t dot title
03:07
And let's say we count this like this.
03:09
Count this by putting the column. Actually, we don't need to call me just put star in there
03:17
And we wanted to where
03:21
u t dot title is equal to student
03:28
and we want to group by because we need to use that with our arrogant functions.
03:31
If we don't use group by, this is what kind of results will get back.
03:37
It just counted everything.
03:39
So screw bidi titled
03:46
and run this
03:50
Oh, you know, Let's take this out.
03:53
There we go.
03:55
So we got 200 for the teacher, 200 for the student
04:00
and we're joined on the user type I D was Type I D.
04:08
Let's go in and change this around a little bit and see if we can change it to something else. So let's say
04:15
you see that
04:17
user, I d
04:20
on that one.
04:24
And of course, we need to put that in the group by Klaus
04:29
on that.
04:31
Okay, so you can see the user. Id's eight is enrolled in 10 courses,
04:38
so he's playing some kind of role in 10 courses.
04:43
So we go back to what we had.
04:46
This is most definitely wrong, and the reason it's wrong is that because we put the indicator of what role the user is playing on the pivot table, that user is going to show up on as a counted result more than once if their student
05:03
in more than one course. For example, if we have user I d. Three
05:08
and three different courses there, count is going to return three,
05:12
and we don't want that because we want to know how many students we have. We don't want to know how many students we have multiplied by how many courses they're taking,
05:25
so to get that
05:26
we actually have to change this query a little bit.
05:33
First we need to get the students that we have. So say you see
05:40
that user I d
05:45
on. Let's say where
05:47
Utd title
05:50
is equal to student.
05:55
So we run this
05:58
on. We need to change the group by statement. Of course.
06:01
Run this.
06:03
This is all the I ds off people that are playing a student role in a course.
06:11
So it is possible we could have someone who is a student and a teacher,
06:15
which
06:15
happens at a lot of schools as well.
06:18
So we have this query which exposes who is a student. But we want to count that. How would we do that?
06:26
Well, an easy way to do that. Which is to make this a derive table put in some brackets
06:32
alias. This was students and then select
06:36
count.
06:41
But actually, we need to ask something in here. We need to add this school I d
06:46
you see that school idea?
06:49
And then, of course, we need a group by that too. Now we're spreading the user type across the whole database.
06:57
So that is why we're still using the school. I d,
07:01
um, against the user I d
07:04
we select this,
07:06
we see the school I d. That each user idea belongs to
07:13
All right,
07:14
so
07:15
now we're still gonna use this as a derive table. We're gonna alias that as students,
07:21
we're gonna select the school I d.
07:30
And we're gonna count Star, which is just miscount everything.
07:34
And we're gonna group by school. I d.
07:39
So this is two layers of aggregation.
07:42
We have the first drive table from that sub query that's using aggregate function. And then we're also aggregating outside that as well.
07:51
What? We do this
07:57
when you say from
08:00
Don't forget those keywords
08:03
by him.
08:09
So
08:11
I think I need to make sure to indicate
08:15
the table name I'm using.
08:26
Oh, silly mistake. Forgot the key word by
08:30
sure you get there, you gotta get your syntax right. Otherwise, it just doesn't work.
08:35
Okay, so and school i d two or school i d to whichever school that is. We know they have 56 students at school one. We know they have 42 students and at school three, we know what they have 47 students. So we have answered the question of how many students we have at each school. We use that by grouping by
08:54
the school idea in the user i d
08:56
and creating a drive table
08:58
and then counting those results. And we used to levels of aggregation on the derive table,
09:05
which I'm highlighting right now with the group by the school idea in the user I d to remove the duplicates from the pivot table.
09:13
We then use that as a derived query sub query. Alias did its students. Then we grouped by the school i D. And we counted the results, and that gave us the number of students per school.
09:26
So let's try another thing or another idea that will be a little bit easier. Let's get thestreet Dent's with their average, uh, average score.
09:37
So what do we need to do that so Well say selects star. So I think we're gonna need the users table
09:45
when alias that I got from the key word from users
09:50
gonna alias that as a you
09:52
I'm gonna join
09:56
the user courses.
10:01
Actually, we're just getting the students with their grades so we don't even need this pavement this pivot table. Just join grades
10:11
now a list that with a G
10:13
on
10:16
g dot user i d
10:18
equals you that i d
10:20
and g dot school I d
10:24
equals you that school i d
10:30
And then we want to slug. Let's get their name.
10:35
It's a first name.
10:37
And then let's just average
10:39
their score.
10:41
They're great. Score grey dot
10:43
Great score.
10:46
And then we need to remember to a group by
10:48
so group
10:50
by.
10:52
We're only using their first name.
10:54
We had other columns that we were grouping by. We, of course, would want to include that in the group by statement as well.
11:01
And
11:03
you know what else we need to do? We need to include the school idea. Because
11:07
this, as written by itself, we would have accidentally aggregated the same person if they happen to have the same first name, which would definitely happen at a large school. We would have averaged their scores together. We don't want to do that. So
11:22
let's just do you that
11:24
I d
11:26
and
11:30
do you that school idea to
11:35
right?
11:37
Well, you will make sure we get this right.
11:41
Go.
11:43
Let's run that.
11:46
All right.
11:46
I've been feeling some numbers changed,
11:50
and here we have We have all the students at the respective school with their average score,
11:56
and that is done.
12:00
So the 2nd 1 was a little bit easier than the 1st 1 we did, because we didn't have to do two layers of aggregation like we did when we were counting it.
12:09
So let's go ahead and head over to this summary
12:13
brings us to the 6.2 summary and this lesson we discuss Abby, it function scope in purpose. On we looked at some simple usages, such as student count and students with average G p A.
12:24
And that completes this lesson. I hope to see you in the next thank you.

Up Next

Intermediate SQL

This free course introduces the student to intermediate concepts found in the implementation and application of Structured Query Language (SQL) within professional business environments.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor