Hello everyone it's Eddie the Magic Monk here. Last lesson we did a query to find
the maximum age of the students so if we run that query you can see we got Sara
and Phoebe both 18 years old so what I want to do now is I want to create a new
query so copy and paste that query select query 8 and then let's edit our
new query close our old query actually we might put it in SQL view that's
that's query 7 let's go to query 8 and basically what we want to do now is I
want to show you what the group by clause represents so let's firstly
select um everyone from students now let's say I want to know how many
students there are ok let's say I want to know how many students there are then
what I can do is simply do a count which just which is just a function that
counts how many rows there are from students and if you run that now you
will get 10 right because there are 10 students now here is where the group by
clause is interesting if I put in here group by gender and then I select gender
comma count star count bracket star what this will do now is it will group the
rows using the genders so if you run this query so design run you get female
and male right so that's a direct response to us saying group by
gender so we put them into groups using the values and gender so it looks for
repeats in the students table looks for repeats so M has repeated five times so
we put them all in the same group F has repeated five times so we put Fs in the
same group so group by gender and then we count how many rows there are in each
group okay so now that's why we can get that's why we can get five each so now
let's go back to our previous query okay and let's copy this over let's copy this
query over and we're just going to modify a little bit so what we had is
basically find the maximum age so let's just go back to just finding the age of
each student so if you run this it lists the age of each student let's get
the gender back up so gender and what we're going to do now is we're going to
find the average age of all the students so if you go to SQL and you put
average in front so let's put AVG bracket and let's put an average here so
if we run that your quarry does not include okay what's going on let's get
rid of that run it well actually we probably need to
get rid of all of this so run it and we've averaged every single student's age
in the table so in the students table if we looked at all their ages from the
previous query okay if I run this query which is our previous query if I run
this query I will get all their ages and if you manually add them all together
and divided by 10 you will get 16 so that's getting the average now if I want
to combine that with the group by gender clause I can actually go group by gender
and then I can list I can find the average age for males and females
so by doing this we click run we get the average age of females is 16.6 and the
average age of males is 15.4 so that's pretty interesting
now we've rounded the ages before they were averaged all right if you wanted to
you could round it again after their average but it's probably ok to round it
before averaging now you can also instead of average you can also use the
maximum age of students so the maximum female age is 18 the maximum male age is
17 as you can see for all the males that we have the oldest person actually let's
run this query out of all the males to John Daniel Bryan Jim and Joey the
oldest guy is only 17 so we can also do the minimum age so
minimum age group by gender so we get the minimum female age is 15 minimum
male age is 14 okay so this concludes our group by tutorial hopefully you
understand a little bit what group-by does now um thanks for watching see you
next time.