SQL Tutorial - 51 Aggregate Functions (Part-1) Video Lecture - Microsoft

hello guys welcome to my channel this is
the fifty-first you told in this course
in this tutorial we're going to check
out some aggregate functions and
aggregate functions as the name suggests
I used to you know work on a bunch of
rows right so they basically get things
together and then you know they do some
stuff for you and we're going to check
out what kind of stuff they can actually
do for you so you know the first one
that we're going to have a look at is
the count star function and this is
probably something that we've used a
couple of times in this course and you
know I might have just you know used it
without explaining the function much so
I saw I thought that you know there
should be a separate tutorial for all
the aggregate functions and you know
that's why I'm going to talk about this
in a bit more detail now so the function
is used to count the number of rows or
records that are returned you know when
you execute a query in a result set
right so you know let's say you want to
find out you know the number of records
that you have in your employee table
right so in that case what you'll do is
type in select and then the name of the
function and then within parenthesis you
type in star right and then you type in
the keyword from and then the name of
the table which is employee so when you
execute it you see that you've got 18
records in the table employee right and
you can verify that by executing a
select star from employee and then
counting all the rows that are there in
the table but then you know this might
not seem too useful for the employee
table because this tables got just 18
rows but then if you've got a table with
like lot of data you know imagine you
know Facebook's database in which it has
a table that has you know details of all
users from a country you know that would
run into millions and millions of rows
so in that case you know the count start
function is very useful it just tells
you how many you know records are there
in a table and you know you can also use
it with the where clause so let's say
you want to find out you know how many
employees are there in the employee
table you know that are less than 20
years old so you type in we're here and
then there's the age field in the tables
so you type in age and then the less
than operator and then 20 right
when you execute this you see that no
rows are returned in the results and
that's because all the employees in the
employee table are more than 20 years in
age so if you want to find out how many
employees are let's say greater than 25
years a in age then you see that there
are 12 employees in total in the
employee table that are more than 25
years old so I just show you guys the
employer table once you know from
employee when you execute it you see
that you would you can make sure that
there is no employee that's less than 20
years old and if you count the number of
employees who have age greater than 25
I'm sure exactly that would be 12 right
so that's the count star function and
the next function that we're going to
check out is the max function and what
that is used for is it tells you the
maximum value for a particular field in
a table right so let's say you want to
find out what is the maximum age you
know of all the employees in the
employee table so what you're going to
do is just get some part of it out max
and then within parenthesis you type in
the field on which you want the function
to work so I wanted to work on the age
field for all the rows right that's why
it's called an aggregate function and
then the from keyword and then the name
of the table so that's employee and you
see that the oldest employee in the
employee in the employee table is 36
years old right and similarly you can
find out the you know least value or you
know the minimum value through the min
function right and let's say we want to
find out the youngest employee in the
table so there you go we see that you
know the youngest employer is in fact 20
years old you know that's why if you
would have executed the query that we
did couple of queries go you know the
one in which we try to find out number
of employees were greater than 20 less
than 20 years old that returns zero but
if you would have used that but less
than equal to operator then it would
have returned one value at least right
so the least value is 20 so thank you so
much for watching this tutorial in the
next tutorial we're going to check out
you know couple more aggregate functions
and then we are going to check out a
couple of DDL commands so there is not
much to cover in this course now we're
almost there almost through and I hope
you guys are enjoying the series and you
may subscribe to my channel if you
haven't already and I'll see you soon