Welcome back to Module six. This is less than 6.3 aggregation queries. Now aggregation queries are simply queries to include the aggregation functions in some form or capacity.
Let's go in and walk through these bullet points. Aggregation queries gather relevant and interesting data from the data trees, much like many other
queries, do their the basis of sequel reporting. So there's are a lot of applications like Crystal reports, Power Bi I and a dozen others where you can write sequel code. You can take that code copy and paste it into the application, and that application will automatically render some nifty reports and charts for you, which allows you to avoid having to write
complex application code that would generate those same charts and reports,
because without those types of applications, that would be a requirement.
So what? That said, we're going to walk through the fallen query. We're going to generate a query that will be the teachers with student count. We want to exclude students with a low average score, so we want students with 90 or higher, and we want to count the number of high performance students each teacher has
now, with that said, Let's go ahead and head over to the development environment.
Welcome back to the development environment. There are a couple fixes to the database files we need to make to really optimize the experience with writing this query.
What that said, Let's take a look at these files and the grade factory,
which is in the Factories folder.
I have changed the minimum from 0 to 65 which means the great will have a minimum score 65 a maximum of 100 which makes it more likely that a grade or a student will have a higher average score
in the database. Cedar file. I have implemented a high number of user courses and a high number of grades 400 to be precise, and I've done that just again to make it more likely that I generate a student that has that has a higher average.
Then, in the user model class, which isn't in the APP folder,
we need to fix the random not enrolled course function.
The UC dot course I d underscore. I d was originally you see dot i d what? You need to switch it. To
course, underscore. I D, and that will help you avoid an air you might have seen where the database cedarwood try to insert a user into a course in which they were already enrolled.
Changing this to the course underscore I d. Will fix that issue.
And once you've implemented those changes, be sure to rerun your database cedar. Otherwise, those changes would be reflected in your database.
Let's go ahead and take a look at the query requirements and work through the query we need to write.
So we need teachers with student count. We want to include students with a lower average score, so we want students with a nine year higher
count the number of high performance students each teacher has.
So a good way to approach these problems is to break it apart and look at the different data points that you need. We're going to need the teachers.
We're gonna need the students with the grades
and we're gonna need teachers who have taught specific students. So this will look like a pivot table in between the grades and students and the teachers table
so that we can connect that relationship together.
So let's go ahead and generate the grades because I think that would be a little bit easier.
So let's select star
when alias that with the U
And then I'm gonna join
grades on Alias that with a G
Tab on G. That school idea
We got to get these connections right. Otherwise, will emerge data that we don't intend emerge
equals you died I d.
Let's go out and run. That's what that looks like.
let's go ahead and minimize the data that we're bringing back a little bit. So you died.
And let's bring back a name you dot
just We'll just do the first name
on then g dot grade score
and let's put an average behind that
or let's put that in an average
then we need a group by these,
but we actually want to keep the school idea in there, too. So let's do that. You school idea.
It's part of our composite primary key.
All right, so we got some students with some averages,
unless use are having claws.
This is how you do a filter with an aggregate function.
there's no e in that
Let's go ahead and run that
greater than or equal to. Not evil too.
So there are high performing students,
so we could put that into brackets like so
which kind of makes it a derive table from a query or a sub query?
If I select star from it
and I going to give it on Alias with HS for High Student,
I need the key word from
so now we need the students. So let's start working on that.
Get rid of the selection. Gonna leave this down here as its own table so that I can connect it up later.
So let's let's get the teachers. It's good. The teachers with the courses that they thought so Select star
actually, we're gonna join
Use the type I d equals u t dot i d
We're gonna add in a filter where u t dot title
so you could avoid adding the user types table if you memorized the i d of the types that were on that table. However, as a query gets more complicated, it's very useful to include the titles so that when you go back and you have to work with the query, you immediately know what you're getting. For example, we know what teacher means intuitively,
but we may not know what one is.
Okay, so we have our teachers. It's going to run this query, Gonna highlight it and hit control. Inner NDB vor.
So this is all my teachers.
So let's go ahead and minimize this query a little bit. So you see that school I d. We're gonna need that.
And let's group by that.
All right, let's run that.
So now we have a list of the schools with the user and the course they taught.
So let's go to put this in brackets and will make this another table,
and we'll go ahead and alias this with
t c for teacher course.
But we don't have the bridge we need across
to get from teacher course to the grades
so Let's get a student course table as well.
So it's almost gonna be exactly like this with one big difference
when alias that within SC
and change this to students.
So let's select star
equals t See that school idea
and a seed? Of course I d
equals t See that course I d.
Let's go ahead and run this to see what this looks like.
So what do we have here we have at school? One
user I D 51 which is probably the teacher. We're gonna label that so that it's more clear. In course, 20
in course. 20. So let's go ahead and minimize what we're getting back a bit.
So let's say SC, that school i d.
So let's do t c dot school idea.
and we'll say TC, that user i d.
And then we'll say TC that
actually it's to SC that
user i d. As a student, I d.
Was that everything we need? Let's go in and run this query, see what it looks like
So we got school ideas at school. One teacher, 51 taught 40 student, 42 in some course.
so now we just need to connect this
to our grades table.
I wonder if our group by would do anything.
I don't think we're getting any duplicates, but we'll be able to see
by grouping by and seeing if our record count changes. But I don't think it's going. Teoh.
Yes, you've got 100 32
131. So okay, there was one duplicate in there,
so we'll leave that group by statement statement in there
because we don't want any duplicates.
So let's turn this into another derive table
with the drive table or sub query within. A sub query within a submarine will say
Call this one teacher student.
Oh, well, just alias it with, uh,
ts for teacher student.
What's going to select star from it?
Let's run this. Make sure it's still working.
Okay, it is still working.
Now we have our grades down here, so let's join this.
I need the key word. Join here
a chest, a school idea. We wanna make sure we have them at the same school
equals siesta school idea.
I d. Which is the user I d
So let's make sure we get some data back from here.
So at school, i d three teacher 18 taught student 1991
whose first name is Fritz and has an average of 96 74
at the same school teacher i d 37 taught student i d 91
the same data, which is what we would expect to see.
this is actually getting closer to what we want, but it's not quite there yet,
so let's select what we want. Want
you see that student I d
on. Then we want, um, close. The last one hs
All right, I got that wrong. So I think it's TS, isn't it
Yes. Normally, we didn't go over table expressions yet, so I'm not using table expressions. But normally I would use table expressions on a
query like this because it keeps things cleaner and more segregated, so you can more easily tell where things are.
All right, so now we wanted to count the number of high performance students that a teacher had.
So we're going to go one more layer up
and we're going to call this
Ah, teacher student High score.
it's good and highlight this and make sure this runs
So now this is where we're going to
We're gonna get rid of this. Gonna put the count function around the student i d.
And then we're going to group by
which is thescore idea in the teacher idea. Let's go in and run this
starting into a pretty gnarly quarry.
See what happened. I don't call him
just that school. I d Did I do the same thing I did before?
Yes, I did. It's TSH.
And here we go. We got our teacher. I ds with our student counts.
So at this point, if I needed to, if this was data that was interesting, and I needed expected to use it in other places. I would probably make this view so that I would not have to deal with writing this or embedding it in other queries.
So now we have a teacher I d. With account of students there high performance.
She's here 37 wins because he has too high performance students.
So that completes this query. Let's head over to the summary. I know this lesson is getting long,
and that brings us to the 6.3 summary. So what did we do in this lesson we implemented in the aggregation query? And that query contained the following elements. It had a having clause, had a group by a clause and, of course, had an aggregate function or two.
So that completes this lesson, and I hope to see you in the next Thank you