SQL Tutorial - 26: The GROUP BY Clause Video Lecture | Microsoft SQL for Beginners - Database Management

hello guys welcome to my channel this is
the twenty sixty toil in this course and
in this tutorial we'll be talking about
the group by clause and the group by
clause as the name suggests is used for
grouping identical data into groups hate
and you know it's used with the Select
query and you know if you've got your
where clause in the Select query then
you know you must make sure that the
group by follows the where clause and if
you've got order by in your query then
you know grew by has to precede the
order by so if you've got all three then
the sequence in which you should have
these clauses is you should have where
first and then group by and then the
order by clause at the end right and
we'll understand how the group by works
through examples and you know it can be
a little tricky to you know be
comfortable with in the beginning but
you know we're gonna check out really
simple applications so you know I'm sure
that by the end of the tutorial you will
guy you'll understand the group by
clause perfectly so the syntax of group
by as I mentioned you know if you've got
all three clauses then you know the
sequence that's that's something that
you have to be careful about you know
that has to be the sequence you know
where and then group by and then order
by and the first part of the query is
pretty simple you know you have your
select keyword and then the column list
and from and the table name so let's see
some examples of group by in the MySQL
environment now so I've got my workbench
and for this tutorial what I have done
is I have created another table and the
name that I've given to the table is
tutorial right and it has a little bit
of information about some of the
tutorials that I have recorded and
shared on YouTube and not all about some
and you know I have recorded tutorials
on three subjects and the subjects are
shell programming and C and SQL so let
me show you the structure of the table
and then we talked about it a little bit
so the name of the tutorial as a name of
the table as I said is tutorial and I'll
execute the query to show you guys the
fields that I have in a table so ID is
one field
this field holds uh you know numbers to
represent key tutorials and then I've
got the title field that holds the title
or the name of the tutorial and then the
subject that is the you know subject to
which the tutorial belongs so if it's a
C tutorial and the subject would be C if
it's a if it's shared programming
tutorial the subject would be shell
programming and the duration you know
this is a decimal field and you know
this is the duration in minutes
so you know if it's 4.5 it means 4
minutes and 30 seconds and then the
upload date or the you know it's it's a
date field and you know I have chosen
three values for this field so all SQL
tutorials you know have the same upload
date in all C tutorials at the same
upload there is no shell programming
tutorials have the same upload date so
I'll show you guys the rows that I have
in the table I've also inserted some
data in it and select star from tutorial
would be the query that I would use to
see the data so you know this is this is
all the information that I have put in
the table so there are some some
tutorials that belong to SQL in some
tutorials that belong to C and some
tutorials that belong to shell scripting
and you know I have also populated the
duration and upload date fields and you
know another thing that you must pay
attention to is that the upload date for
all SQL tutorials is the same and that
is 8th August 2013 the upload date for
all 0 is the same that's 4th April 2013
and the upload date for all is s shell
scripting tutorial is also the same
which is first January 2013 dry heat and
there's nothing else to note in this
table and what I'm gonna try to do now
is I'm going to try to find out the
number of tutorials I have for each
subject right and you know just think
about it I want to you know find out the
number of vetoes I have an each subject
so I would have to first create groups
of subjects right so I would put all the
SQL tutorial
in one group and I would put all these
CD dollars in one group and all the
shell scripting tutorials in one group
and these groups would not be created
within the table but when you you know
pull our information from the table when
you see the results in the results set
you know they can be arranged into
groups at that time and you can see you
know the arrangement the group
arrangement you know in the result set
so we're gonna find out the number of
tutorials that we have in each group so
we'd have to use the count function and
the count function is an aggregate
function and I haven't you know
discussed it yet but I thought that you
know I would discuss it when we you know
discuss all the other aggregate
functions but you know we'll use it now
and it's really simple there's hardly
anything in it to understand so I'll
type in select first and then the
subject field because you know you want
to see the subject names as well and
then I'll type in the keyword count and
within parenthesis I'll type in a strict
right so this is going to return the
number of records you know that the
result set is going to get to us and on
the next line I'll type in the keyword
from and then the name of the table
which is tutorial and on the next line
I'll type in Group pie and then I'll
type in the field on which I want the
group's to be created and that in this
case would be subject and I'll put a
semicolon at the end and when I execute
this query I see that you know I have
applauded a tutorials on C and for
tutorials on shell scripting in five
tutorials on SQL well we already have
uploaded you know a lot more but these
are all the rows that I have in the
table at the moment so the count start
function I hope you know you guys not
confused about it it just helps us you
know calculate the number of records
that are present in each group right so
first the Select star query was executed
then the groups were clubbed together
and so the tutorials were clubbed
together into groups and then the number
of rows that word you
put together in each group what returned
you know through the count start
function and next if I want to find out
the duration of each playlist right so
the way I'm going to do that is again
I'm going to group by subject but this
time I'm going to use another aggregate
function or the count start function but
the sum function right and I sum on the
duration field right and when I execute
this query I see that the you know
duration of the playlist on C is 48 0.17
and the duration on of the playlist on
shell scripting is sixteen point eight
eight and the duration of the playlist
on SQL is twenty five point five five
and again you know I I didn't do
anything fancy it's pretty simple I've
just used this function and we haven't
seen this in this course and we will be
having tutorials on aggregate functions
and you know I talked about count and
summon some more aggregate functions in
that tutorial but the time being just
know that this adds numbers together so
the groups again were created on the
subject field and you know the values
that are present for each record in the
duration field those values were added
for each group right and then the values
were displayed in the result side so I
hope the group by clause is clear to you
guys and in case you have doubts and you
can send me messages and or you can
comment on the video and I'll try to
reply and thank you so much for watching
this tutorial I'll see you the next one
in which we'll discuss something
interesting and please subscribe to my
channel if you haven't already and as
usual