0:02
[Music]
0:06
thank you
0:09
[Music]
0:12
Hello friends welcome back to our
0:13
channel so in today's session we'll
0:15
discuss about One More Concept in dbms
0:18
that is
0:19
aggregate functions in SQL
0:30
aggregate functions in SQL and these are
0:33
the functions which will be evaluated on
0:38
a different values of a column right so
0:42
the first one is
0:45
count
0:46
function
0:48
which will give the
0:51
count of
0:52
values
0:55
often
0:57
attribute
0:59
attribute so these functions these
1:02
aggregate functions will be applied on a
1:04
column okay individual columns so if you
1:07
use this count function it will give the
1:10
number of values available for that
1:12
particular field okay
1:14
then
1:19
some function so this will be applied
1:21
for a numerical values okay it will be
1:24
applied for
1:25
numerical
1:27
values
1:29
and it will apply the
1:31
sum of
1:34
all values
1:41
often attribute so if you consider some
1:45
attribute like subjects or a percentage
1:49
so it will give the complete sum of all
1:51
the percentages okay then similarly if
1:55
someone more function called average
1:59
so this will also be applied for the
2:01
numerical values and it will give the
2:03
average
2:07
of all values
2:11
right similarly
2:14
minimum
2:16
it will give the minimum
2:21
value
2:25
of an added value
2:29
so here the attribute means a column
2:31
right here the attribute means a column
2:33
so in that particular column there will
2:36
be a different values that means a
2:37
different rows right so among those
2:40
values it will return the minimum value
2:42
similarly among all those values it will
2:44
return the average value
2:46
among all those values it will give the
2:48
sum okay and this will give the count
2:51
the number of values available for that
2:53
particular column
2:56
similar to our mean
2:58
called maximum which will give the
3:01
maximum value
3:04
maximum value
3:08
of an attribute
3:10
value of an atom so these are the
3:13
aggregate functions of in SQL right so
3:17
in sessions we have discussed about the
3:20
select statement and in that select
3:22
statements we left two concepts that is
3:25
grouped by and having so in order to
3:28
know about the group by having first we
3:31
have to know about these aggregate
3:32
functions
3:33
so in this session we'll see the
3:36
aggregate functions and in the next
3:38
session we'll see the group by n having
3:42
class in select command okay so these
3:46
are the different aggregate functions
3:49
now let us execute each and every
3:51
function in a MySQL so already we have
3:54
created one database and in the database
3:56
we have already created a table right so
3:59
we'll apply these aggregate functions on
4:02
the attribute of that particular table
4:04
right so let's move on to the MySQL
4:07
command line interface
4:09
Hello friends so just now we have seen
4:12
the aggregate functions which are
4:14
available in SQL so let me demonstrate
4:17
each and every aggregate function this
4:19
MySQL so let me open this one so giving
4:23
the
4:24
password
4:25
so just I'll clear the screen now we
4:29
have created one database called YouTube
4:30
so let us use that particular database
4:33
and in that database we have created one
4:36
table I think yes there is a student so
4:39
see first letter let's set what are the
4:42
fields available in that first students
4:44
so whether the data is available in the
4:46
student table or not so in order to
4:48
retrieve the data select star from
4:52
screw it
4:54
yeah having around 6
4:56
student details
4:58
now so what's the first step
5:01
aggregate function it's a count
5:04
right so count function
5:12
syntax
5:14
so count of give the attribute
5:18
attribute name
5:21
right so here
5:25
select
5:26
count of
5:28
yes name
5:29
count of yes name
5:33
right from
5:35
student so we'll get the count of that
5:39
particular s name
5:41
right so let us create one more uh I
5:45
mean let us check select
5:48
count of Branch let's check this one
5:51
branch
5:52
from student
5:54
so we got a six count okay so among
5:57
these six you can observe three are from
6:00
each CSE and one from ECE and civil and
6:04
Tripoli so what is the distinct count
6:06
here so the distinct count is one two
6:10
three and four so we can also select
6:12
this one select
6:14
count of use the keyword distinct
6:17
distinct branch
6:20
from student
6:22
see we got a distinct brand that means
6:25
the unique count so it will avoid the
6:28
repetitions it will give a unique
6:29
account and simply you can also give the
6:32
select so count
6:35
star which will give you the complete
6:37
count from student
6:39
C
6:40
so there are six rows it
6:43
it implies that
6:44
index rows in the
6:46
table
6:47
right so this is the count function this
6:50
is a count function
6:51
similarly sum function
6:55
some function so some function is also
6:57
similar so
7:01
the Syntax for some function
7:07
sum of
7:10
attribute
7:12
name
7:14
so here select let us check select star
7:18
from student
7:22
so there is a percentage
7:24
so that's the only numerical thing so
7:27
select sum of
7:30
percentage from student so you'll get
7:34
the complete sum of all the values of an
7:38
attribute so here the attribute is
7:39
percentage so whatever the value is
7:41
available in this particular percentage
7:43
will be added and that will be written
7:45
as a sum
7:48
okay then
7:50
what's the next one the next one is
7:52
average
7:57
the Syntax for this average is avj
8:01
followed by attributes name
8:05
so select
8:07
average of percentage
8:10
from
8:12
student
8:13
so this is the average of a percentage
8:15
so we have had the percentage of six
8:18
students so among these six students
8:21
what's the average percentage
8:23
the average percentage is some
8:25
91.333 and so on
8:28
so this is the average function
8:31
then
8:32
next one a minimum
8:35
minimum so in order to get the minimum
8:38
value the syntax is min of attribute
8:43
name
8:45
so here you can see select Min of
8:49
percentage
8:50
from
8:52
student so this is the minimum
8:54
percentage so you can see among all the
8:57
percentages of students so this value is
9:00
the minimum so that's why we got 78 as a
9:04
result the minimum percentage
9:07
and then the next one is
9:11
maximum
9:13
maximum so the syntax is similar to our
9:16
minimum function will give a maximum of
9:18
attribute
9:23
name
9:24
so select
9:26
Max of
9:29
percentage
9:31
from student
9:34
so we got a maximum percentage so among
9:37
all the percentages you can see the
9:38
maximum value is 99 so we got this one
9:41
maximum percentage
9:43
right so this is how we can
9:48
execute the
9:49
aggregate functions so select
9:53
count of attribute name
9:57
from
9:58
table name so this is the syntax
10:00
and here also you need to use a select
10:03
sum of attribute name
10:06
from table name
10:10
and here also
10:13
select average of attribute name
10:16
from
10:18
table name
10:24
select minimum of attribute name
10:27
from
10:29
table name right and here also you can
10:32
get select Max of attribute name
10:36
from
10:37
table name right so this is the
10:40
aggregate functions all these are the
10:42
aggregate
10:45
functions
10:48
SQL or we can simply call as SQL
10:51
aggregate functions SQL aggregate
10:54
functions
10:56
right so hope you understood the Syntax
10:58
for all the aggregate functions and we
11:01
we are going to use these aggregate
11:02
functions in the queries okay and in the
11:06
next class we will see about
11:09
the group by class
11:11
as well as having class so in that we
11:15
are going to use these aggregate
11:16
functions so those class that means are
11:19
grouped by as well as having will be
11:23
used in a select command there we are
11:25
going to use this aggregate functions
11:27
right so hope you understood this one
11:29
let's stop here and if you are having
11:30
any doubts regarding these aggregate
11:32
functions feel free to post your doubts
11:34
in the comment section definitely I will
11:35
try to clarify all your thoughts if you
11:37
really enjoyed my session like my
11:39
session share my session with your
11:41
friends and don't forget to subscribe to
11:43
our Channel thanks for watching thank
11:45
you very much