Aggregation Queries

00:00

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.

00:10

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

00:17

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

00:37

complex application code that would generate those same charts and reports,

00:41

because without those types of applications, that would be a requirement.

00:45

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

01:02

now, with that said, Let's go ahead and head over to the development environment.

01:06

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.

01:14

What that said, Let's take a look at these files and the grade factory,

01:19

which is in the Factories folder.

01:22

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

01:36

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.

01:51

Then, in the user model class, which isn't in the APP folder,

01:56

we need to fix the random not enrolled course function.

01:59

The UC dot course I d underscore. I d was originally you see dot i d what? You need to switch it. To

02:07

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.

02:16

Changing this to the course underscore I d. Will fix that issue.

02:22

And once you've implemented those changes, be sure to rerun your database cedar. Otherwise, those changes would be reflected in your database.

02:29

Let's go ahead and take a look at the query requirements and work through the query we need to write.

02:35

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

02:42

count the number of high performance students each teacher has.

02:46

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.

02:53

We're gonna need the students with the grades

02:55

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

03:06

so that we can connect that relationship together.

03:08

So let's go ahead and generate the grades because I think that would be a little bit easier.

03:15

So let's select star

03:17

from users

03:21

when alias that with the U

03:23

And then I'm gonna join

03:29

grades on Alias that with a G

03:32

put on right there.

03:36

Tab on G. That school idea

03:42

you dot school i d.

03:44

We got to get these connections right. Otherwise, will emerge data that we don't intend emerge

03:49

Angina user idea

03:52

equals you died I d.

03:55

Let's go out and run. That's what that looks like.

04:00

All right,

04:01

let's go ahead and minimize the data that we're bringing back a little bit. So you died.

04:05

I

04:06

you dot i d

04:10

And let's bring back a name you dot

04:14

just We'll just do the first name

04:16

on then g dot grade score

04:20

and let's put an average behind that

04:24

or let's put that in an average

04:29

as average,

04:33

then we need a group by these,

04:36

but we actually want to keep the school idea in there, too. So let's do that. You school idea.

04:42

It's part of our composite primary key.

04:46

So

04:50

the group by

04:56

All right, so we got some students with some averages,

05:00

unless use are having claws.

05:04

This is how you do a filter with an aggregate function.

05:10

So having

05:12

there's no e in that

05:19

greater than

05:20

for equal to 90.

05:24

Let's go ahead and run that

05:28

oh

05:30

greater than or equal to. Not evil too.

05:33

So there are high performing students,

05:36

so we could put that into brackets like so

05:40

which kind of makes it a derive table from a query or a sub query?

05:45

If I select star from it

05:47

and I going to give it on Alias with HS for High Student,

05:53

I run this.

05:56

I need the key word from

06:00

I run this.

06:03

Okay,

06:05

so now we need the students. So let's start working on that.

06:11

Get rid of the selection. Gonna leave this down here as its own table so that I can connect it up later.

06:16

So let's let's get the teachers. It's good. The teachers with the courses that they thought so Select star

06:25

from

06:29

user courses,

06:30

you see,

06:35

actually, we're gonna join

06:39

during user types

06:41

U t

06:43

on.

06:44

You see that?

06:46

Use the type I d equals u t dot i d

06:50

We're gonna add in a filter where u t dot title

06:56

people's teacher

06:58

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,

07:16

but we may not know what one is.

07:20

Okay, so we have our teachers. It's going to run this query, Gonna highlight it and hit control. Inner NDB vor.

07:29

So this is all my teachers.

07:31

So let's go ahead and minimize this query a little bit. So you see that school I d. We're gonna need that.

07:39

See that

07:41

user? I D.

07:43

And

07:46

that course idea

07:46

And let's group by that.

07:56

All right, let's run that.

07:59

So now we have a list of the schools with the user and the course they taught.

08:05

So let's go to put this in brackets and will make this another table,

08:11

and we'll go ahead and alias this with

08:16

Let's see him

08:18

t c for teacher course.

08:22

But we don't have the bridge we need across

08:26

to get from teacher course to the grades

08:30

so Let's get a student course table as well.

08:35

So it's almost gonna be exactly like this with one big difference

08:39

when alias that within SC

08:41

and change this to students.

08:50

So let's select star

08:54

from

08:56

teachers

08:58

T. C.

09:01

We're gonna join

09:05

this table SC

09:09

on

09:13

See that

09:13

school? I d

09:18

equals t See that school idea

09:24

and a seed? Of course I d

09:31

equals t See that course I d.

09:37

Let's go ahead and run this to see what this looks like.

09:41

So what do we have here we have at school? One

09:46

user I D 51 which is probably the teacher. We're gonna label that so that it's more clear. In course, 20

09:54

taught student 42

09:56

in course. 20. So let's go ahead and minimize what we're getting back a bit.

10:01

So let's say SC, that school i d.

10:05

So let's do t c dot school idea.

10:11

That's the school,

10:13

and we'll say TC, that user i d.

10:18

As a teacher, i d.

10:24

And then we'll say TC that

10:28

actually it's to SC that

10:33

user i d. As a student, I d.

10:39

Was that everything we need? Let's go in and run this query, see what it looks like

10:43

So we got school ideas at school. One teacher, 51 taught 40 student, 42 in some course.

10:58

All right,

11:01

so now we just need to connect this

11:05

to our grades table.

11:07

I wonder if our group by would do anything.

11:11

I don't think we're getting any duplicates, but we'll be able to see

11:16

if we are

11:20

by grouping by and seeing if our record count changes. But I don't think it's going. Teoh.

11:26

Yes, you've got 100 32

11:28

group by

11:31

131. So okay, there was one duplicate in there,

11:33

so we'll leave that group by statement statement in there

11:39

because we don't want any duplicates.

11:43

So let's turn this into another derive table

11:48

with the drive table or sub query within. A sub query within a submarine will say

11:52

Call this one teacher student.

11:58

Oh, well, just alias it with, uh,

12:01

ts for teacher student.

12:03

What's going to select star from it?

12:09

Let's run this. Make sure it's still working.

12:11

Okay, it is still working.

12:15

Now we have our grades down here, so let's join this.

12:20

So

12:22

I need the key word. Join here

12:28

on

12:30

a chest, a school idea. We wanna make sure we have them at the same school

12:33

equals siesta school idea.

12:39

And

12:43

I see a chest that

12:46

I d. Which is the user I d

12:50

equals.

12:54

He s got

12:56

student I d.

13:03

So let's make sure we get some data back from here.

13:09

All right?

13:09

So at school, i d three teacher 18 taught student 1991

13:16

whose first name is Fritz and has an average of 96 74

13:22

at the same school teacher i d 37 taught student i d 91

13:26

the same data, which is what we would expect to see.

13:33

So

13:33

this is actually getting closer to what we want, but it's not quite there yet,

13:39

so let's select what we want. Want

13:43

t c dot school idea

13:48

on. We want t see

13:50

that teacher I d

13:54

on. Then we want

13:58

you see that student I d

14:03

on. Then we want, um, close. The last one hs

14:09

h s that

14:11

first name

14:15

and then hs dot

14:16

average.

14:20

All right.

14:24

And that

14:26

All right, I got that wrong. So I think it's TS, isn't it

14:31

t s? Yes.

14:33

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

14:41

query like this because it keeps things cleaner and more segregated, so you can more easily tell where things are.

14:50

All right, so now we wanted to count the number of high performance students that a teacher had.

14:56

So we're going to go one more layer up

15:03

and we're going to call this

15:07

Ah, teacher student High score.

15:11

So select star

15:13

from

15:16

it's good and highlight this and make sure this runs

15:20

and it still does.

15:22

So now this is where we're going to

15:28

do our accounts.

15:33

We're gonna get rid of this. Gonna put the count function around the student i d.

15:39

And then we're going to group by

15:43

these columns,

15:48

which is thescore idea in the teacher idea. Let's go in and run this

15:52

starting into a pretty gnarly quarry.

15:58

See what happened. I don't call him

16:00

just that school. I d Did I do the same thing I did before?

16:07

Yes, I did. It's TSH.

16:15

Now let's run that.

16:19

And here we go. We got our teacher. I ds with our student counts.

16:25

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.

16:38

So now we have a teacher I d. With account of students there high performance.

16:42

She's here 37 wins because he has too high performance students.

16:48

So that completes this query. Let's head over to the summary. I know this lesson is getting long,

16:53

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.