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 the two more Clauses if
0:18
select command that is a group by and
0:20
having cross
0:22
so Group by
0:25
and having class
0:30
so these clauses
0:33
so these clauses
0:36
will be used in
0:40
select statement or select command
0:49
select Command right and see this
0:54
select the command is used to
0:57
retrieve the data retrieve the data from
0:59
the table so in this select command we
1:03
have already seen the where class and
1:05
the ordered by class so where class is
1:08
to get the data based upon the
1:09
conditions and
1:11
the next one order by class is used to
1:15
order I mean the getting the data in
1:18
either ascending order or the this any
1:20
code similarly this group by
1:24
Group by class is used to
1:28
group the class group the rows so
1:31
whatever the rows we are having so group
1:33
the rows based upon the column
1:36
based upon the column value
1:40
right so based upon the column value and
1:42
here we are supposed to use the
1:43
aggregate functions so which we have
1:45
covered
1:46
in the previous session aggregate
1:48
functions like sum
1:52
average minimum maximum count etcetera
1:55
Etc we have seen these aggregate
1:56
functions in the previous session so we
1:58
are making use of those aggregate
2:00
functions in this group by class
2:01
similarly having
2:04
this having class here also we are going
2:07
to use the aggregate functions
2:12
and here
2:14
we will apply the conditions
2:18
conditions
2:19
on
2:21
a result
2:23
of
2:24
by class
2:28
so after applying this group by class we
2:32
can apply the conditions by using this
2:33
having
2:35
having Clause right so this group by
2:38
means grouping the rows so the resultant
2:41
rows will be
2:42
retrieved based upon the column so that
2:45
means a group by column
2:47
column okay Group by column for example
2:48
so in our uh
2:52
actually in the previous sessions we
2:53
have
2:54
taken some database called a student
2:56
right student database in the student
2:59
database we have taken the student ID
3:02
student name for example let it be the
3:05
gender
3:06
gender right
3:10
so we can group I mean we can get the
3:13
details
3:14
of particular rows grouped by this
3:17
gender that means male and a female so
3:20
we can count okay how many number of
3:22
students are male students and how many
3:25
students are a female so that can be
3:28
done with the help of this group by so
3:31
we'll use account method so count means
3:34
which is an aggregate function grouped
3:36
by
3:38
Group by
3:39
gender so automatically we will get the
3:42
count of male and female
3:44
right so like that we are going to
3:48
get the summary of rows based upon the
3:51
column that means Group by a column so
3:54
that group can be either based upon a
3:57
single column or multiple columns or a
4:00
multiple columns
4:01
right so here also we are going to this
4:04
aggregate function whatever the summary
4:06
we are getting here we can apply the
4:08
conditions and get the result
4:10
okay we can apply the conditions and get
4:12
the result
4:13
right so hope you understood this one so
4:16
still if you are having any doubts so
4:17
now let us execute these two things for
4:20
a student table
4:22
okay in MySQL so I'll demonstrate these
4:25
two classes by executing a small query
4:27
uh on the student database okay so that
4:31
if you are still having any doubts your
4:34
doubts will be get clarified so let's
4:36
move on to the MySQL
4:38
Hello friends so just now we have seen
4:41
the introduction part of group by and
4:44
having classes now let us demonstrate
4:47
this group by in having classes of
4:49
Select command
4:51
so first let me open the MySQL
4:55
so go with the sorry
4:58
password
5:00
root
5:01
let's let us clear the screen
5:04
now
5:06
see let us execute a few queries of that
5:10
uh demonstrating these two things
5:18
yes Group by
5:21
and the Syntax for this group by
5:24
is
5:29
so we are going to use this group by and
5:31
having in a
5:33
select command so select
5:37
star
5:41
from
5:43
table name
5:46
Group by
5:49
column one
5:51
column two
5:53
Etc so this is a small syntax
5:57
for group by
5:59
and coming to this having class
6:02
the syntax
6:09
select start from student
6:14
sorry Start From table name
6:19
table name Group by
6:23
column one
6:26
column two Etc
6:29
having
6:31
condition
6:33
right so this is a Syntax for having
6:35
class
6:36
so whatever the resultant table we are
6:39
getting from the group by applying the
6:41
conditions for that we'll get this
6:43
having class so whatever it may be we
6:45
are going to use this group by class as
6:47
well as the having class in a select
6:50
Command right so let us take an example
6:54
so already we have we are using one
6:56
database called YouTube we have created
6:58
the database sorry so in that YouTube
7:01
database we have already created one
7:03
table called a student so first let us
7:06
see the details of that stage student
7:09
table so let's start from student
7:11
so we are having some rows
7:14
some 106 rows that means the details of
7:18
106 so we are taking the attributes Sid
7:22
s name percentage gender and a branch
7:25
now coming to the group by
7:28
see first one finding number of students
7:30
Branch wise so we have to group the
7:33
number of students Branch wise so for
7:36
that we have to write so select we need
7:40
to get a branch from a count of Sid that
7:44
means for each branch how many number of
7:46
students are there so from student
7:49
Group by use a group by give it as a
7:52
breath so obviously Group by Branch
7:55
means based upon the branch it will
7:58
display the branch name as well as the
8:00
count of the student details you can
8:01
observe here see after executing this
8:04
one the CAC the count of Sid is three
8:06
you can observe the table CAC one two
8:10
and three so there are three students
8:12
belongs to
8:13
this CAC branch and ECE only one student
8:18
and civil one student and a triple one
8:21
student so we got the details of
8:23
students based upon the branch so we are
8:26
grouping the results grouping the rows
8:28
based upon the branch
8:31
right next
8:34
finding the number of male students and
8:36
the female students so how many number
8:39
of male students and how many number of
8:41
female students so for that we have to
8:44
group by gender we have to group by
8:47
gender similar to our previous query so
8:49
use a gender
8:51
count of Sid
8:54
from student now we have to group by
8:58
gender So based upon the gender we'll
9:00
get so male students three female
9:03
students three now let us check with
9:04
this one start from student
9:07
she made students three so one two and
9:11
three and a similarly female students
9:13
one two and three so we are grouping the
9:16
complete rows based upon the gender so
9:19
automatically we are having a distinct
9:21
uh count of gender is true so we are
9:24
getting only two values based upon this
9:27
one we are getting the count
9:29
right so this is how we can use a group
9:32
by and the third one finding average
9:34
percentage of male students and the
9:36
female students we need to give get the
9:39
average percentage of all among all the
9:41
male students among all the female
9:43
students here also we need to group by
9:46
the gender right because we are asking
9:49
about the percentage of male and female
9:52
students now use a query select a gender
9:56
comma use aggregate functions average of
10:01
percentage
10:02
from student
10:05
from student
10:06
grouped by
10:08
gender
10:09
so male students average is 94 and the
10:13
female students average is 88. so you
10:16
can observe here let us
10:18
checkout so here the male students
10:21
average 99 Plus
10:24
another male 95
10:26
plus another 188
10:29
we are calculating the average so
10:31
divided by 3. so we got a 94 for male
10:34
students yes correct and coming to the
10:37
three students let
10:41
78
10:42
plus another one 98. and here also we
10:46
need to divide it by 3 average because
10:48
it's average 88.666
10:51
so we got a average percentage among all
10:54
the male students among all the female
10:56
students
10:58
next one maximum percentage from male
11:01
and female students here also we are
11:03
grouping based upon the
11:05
gender right so select
11:08
maximum of percentage
11:12
okay uh
11:14
based upon gender no so
11:16
gender comma maximum percentage
11:20
from student
11:22
Group by gender So based upon the gender
11:25
we are getting the maximum and a minimum
11:27
so male male students among male
11:30
students the maximum percentage is 99
11:32
and among the female students so it's a
11:36
98 so 98 in male and 98 in a female
11:42
right so this is how we can write the
11:45
query we can Group by gender we can
11:49
Group by gender and if you just want to
11:52
get a branch wise
11:59
all right
12:01
so if you want to get the maximum
12:03
percentage among the branch
12:06
so see CAC it's 99 and all the remaining
12:09
only one student so we'll get 90 88 and
12:12
9 10. so coming to the CAC see 99 95 and
12:16
78 among these three 99 will be the
12:18
maximum and remaining branches only one
12:20
student is there so it will be a maximum
12:22
so we'll get this one so in this case we
12:24
are grouping by Branch we are grouping
12:27
by branch
12:28
right similarly the next one minimum
12:31
percentage the similar query so select
12:34
uh
12:35
similarly write down a branch and a
12:38
minimum of percentage from student
12:41
Group by
12:43
Branch so you need individual Branch
12:45
what is the minimum percentage so now
12:47
you will getting the CAC as a 78 you can
12:49
observe here CSA 99 and 95 and 78 three
12:54
students right among these three
12:56
percentage 78 is the least percentage so
12:58
we are getting the 78 here
13:00
so similarly based upon the gender you
13:02
can also use by gender so
13:05
you can get
13:07
gender and a minimum of percentage now
13:10
we are going to group by
13:14
gender so among the male students 88 is
13:19
the least percentage and among the
13:21
female students the least percentage is
13:23
the 78. see let us check
13:26
select star from student
13:30
among male 98 95 and 88
13:34
so 88 will be the least female 90 78 and
13:38
98 so 78 will be the next
13:41
so like this we can apply the group by
13:44
we can apply the rows the summary of the
13:46
rows grouping by a particular columns
13:50
right and coming from coming to this one
13:54
so after getting the results of this
13:56
group by applying the conditions will be
13:59
using with the having class so select
14:02
start from table name Group by column
14:04
one column two having a condition right
14:07
see
14:08
finding the branches having only one
14:11
student
14:12
finding the branches having only one
14:15
student so we need to group by Branch
14:18
okay Group by Branch now use this one
14:21
select get a branch and a count of
14:25
branches
14:28
from student
14:30
Group by
14:32
branch and what we have to get only one
14:36
student so
14:37
count off
14:39
Sid is equal to 1. so here also we need
14:43
to use the aggregate function
14:46
sorry here Group by branch and here we
14:49
need to write the having class having
14:50
count Sid is equal to one so actually we
14:53
are getting the count of branches cac3
14:56
ec1 civil one and triple one but here we
15:00
are finding the branches who is having
15:03
exactly one student so we got only one
15:06
stone in the branches who have only one
15:08
student so these are the three branches
15:10
if you just change this one the second
15:12
one see Finding branches having more
15:14
than one student
15:16
so you can observe here
15:18
greater than 1. so obviously we'll get
15:21
so only the CSC because except the CSA
15:23
all the remaining branches are having
15:24
only one student so let us count here
15:26
select
15:28
star from student and here you can
15:30
observe so CAC one two three students
15:34
belongs to CSE and ECE one civil one and
15:37
typically one so here we are finding the
15:39
branches who are who are having who is
15:42
having the students number of students
15:44
greater than one so obviously we'll get
15:47
only one branch because only CS is
15:48
having three students right so this is
15:52
how so whatever the result we are
15:54
getting after applying the group by and
15:57
applying the conditions on that
15:58
particular result will get the habit
16:01
right next uh finding branches in which
16:04
students having a minimum percentage
16:07
greater than 70 that means uh finding
16:10
the branches
16:11
in which the students minimum percentage
16:14
is a greater than 70. okay in which a
16:16
student's minimum percentage
16:18
so for this we can write so cell branch
16:22
and we have to find the minimum
16:25
percentage
16:26
from a student
16:28
student
16:30
Group by
16:33
branch
16:34
having minimum of percentage
16:39
greater than 70.
16:41
so you can see among the CAC
16:45
among the CSE the minimum
16:48
percentage is 78 which is greater than
16:51
70. so among this ECE only one student
16:54
90 so it will be at the maximum and
16:57
minimum so let us insert a few more rows
17:01
and we will check okay so you insert
17:05
into student
17:09
values
17:12
107
17:14
so let us take the name
17:18
ramu
17:22
and the percentage is some 6. 16.
17:28
okay and gender May
17:31
[Music]
17:32
and easy
17:37
right now select star from student we'll
17:40
get one more Row one or seven now we can
17:43
check
17:45
greater than 70 you can observe here the
17:48
minimum percentage greater than 70 there
17:51
is no EC because EC there are two EC
17:54
students right one with 90 percent and
17:57
one with sixty percent so among with
17:59
among these two percentages what's the
18:01
minimum percentage it says 60 Which is
18:04
less than 70. so it will be not
18:06
displayed so our query is our query is
18:09
finding the branches in which students
18:12
is having the minimum percentage which
18:14
is greater than 70 so the minimum
18:17
percentage of a student should be
18:18
greater than 70. so obviously the easy
18:21
student will not be in the resultant
18:25
right so hope you understood this one
18:27
similarly the last one finding branches
18:30
in which student having a maximum
18:31
percentage is greater than 90 so among
18:34
this one
18:37
same
18:38
maximum percentage the student's maximum
18:41
percentage should be
18:43
greater than 90.
18:47
greater than 90 let us check
18:51
foreign
18:59
let us check select star from student so
19:04
among CAC among CSC
19:07
99
19:09
95 and 78 among this one maximum
19:12
percentage is 99 which is greater than
19:14
90. so we are getting CH as a result
19:16
coming to the ECE 90 and 16. so both are
19:22
not greater than 90. so here we are
19:24
giving only greater greater than it's
19:26
not a greater than or equality so EC
19:29
will not be displayed here coming to the
19:31
Civil only one student which is 88 which
19:33
is not greater than 90 and Tripoli which
19:36
is 98 which is greater than 90. so we
19:38
got it to be as this one
19:40
if you check here
19:42
greater than
19:47
80. so we'll get all the branches
19:50
because among CAC the maximum one
19:53
maximum percentage is 99 which is
19:55
greater than 80 and among the EC 19
19:57
which is greater than 80 then civil
19:59
which is greater 88 which is greater
20:01
than 80 80 and triple 98 which is
20:04
greater than 80. so like this we can
20:07
apply the grouping and of further
20:10
resultant we can check the conditions by
20:13
using the having class we can check the
20:15
having class right so this is how we can
20:19
use this group by class and have in
20:22
class
20:23
in select a command
20:26
so these these classes will be used to
20:28
retrieve the data from the table by
20:32
using select command right so hope you
20:35
understood this one so let's stop here
20:37
and if you are having any doubts
20:38
regarding this group by and having feel
20:41
free to post your doubts in the comment
20:42
section definitely I will try to clarify
20:44
all your doubts and if you really
20:46
understood my session like my session
20:48
share my session with with your friends
20:50
and don't forget to subscribe to our
20:52
Channel thanks for watching thank you
20:53
very much