MySQL Database Tutorial - 19 - GROUP BY Video Lecture | MySQL (Sequential Query Language) Database Tutorial - Software Development

what is going on guys welcome to your
19th MySQL tutorial and I want to show
you guys a nice little tip that's going
to save you guys a whole bunch of time
in this tutorial so in the last tutorial
what I did is I showed you guys how you
could find a number of listings for each
seller so let's go ahead and run an
aggregate function for example if we
wanted to find the number of listings
for each seller we would go ahead and
select seller underscore ID and we would
also do something like count all and
what this would do is count all their
listings and I'm just going to name this
as item count from items now in order to
get an individual seller what we would
do is we would add a where clause for
example where seller underscore ID
equals one now what this line of code
would do was basically get the seller ID
and count their items where the seller
ID was equal to one so let's go ahead
and hit go and obviously seller ID is
selling two items okay now we need to do
this first seller number two seller
number two let's go ahead and run this
they are selling two items as well okay
how many people do we have in this oh
wow we have like 100 different sellers
okay let me go grab a book this is going
to take some time or is it what if there
is the easier way that we could do this
instead of using this where clause what
we could do is we could use a new little
trick called group by and instead of
having the type each individual seller
let me just go ahead and show you this
whenever use group by just go ahead and
type what you want to group by for
example seller ID and what this is going
to do is it's going to list the seller
ID and the number of items for each
seller ID in other words for each seller
so again instead of having a bunch of
different where clauses for each seller
we can just go ahead and group them by
seller ID and it's going to do all of
this automatically for us so let me go
ahead and hit go and check this out how
amazingly aw
some is this again using group I we can
see that seller one is selling two items
the next seller has two items listed and
let's see like this seller has five
items listed the seller has four items
listed it's a lot easier than having to
type each seller individually pretty
dang sweet huh now furthermore you can
also filter the results of this for
example let's say that we only wanted to
list the users that have three or more
items listed on our website we only
wanted to list the power sellers so in
order to do that go up to your query and
of course we need to still group by
seller ID and now instead of a where
clause you use a keyword called having
now having is kind of like the wear of
groups so having and then you write the
test we want to list all sellers having
account in other words having items
listed that are greater or equal to I
don't know let's just say greater equal
to three so now not only is it going to
list all of the sellers and the number
of items but it's only going to list the
ones that have a count higher than 3 and
that kind of looks like the kind of
reminds me is something I can't tell
exactly what my anyways whenever we list
this check it out now the only sellers
that are listed are the ones that have
an item count of 3 or higher 3 4 5 etc
so now if you want to list like the top
sellers for your website or something
this is how you would do that again one
last time having is basically it's
similar to the where clause where you
can give a test and it can filter your
results just remember this whenever
you're using groups remember to use the
keyword having whenever you're just
using rows like a typical test use the
where clause but having is for groups
where is for just basic tests so the
last thing I want to tell you guys in
this story is
you can also order by a group for
example you see how we got the list of
all the power sellers all of the sellers
who listed three or more items well I
want to arrange this a little bit
prettier I want to arrange the sellers
from highest to lowest the people who
have the most items listed to the people
who have the lowest items listed or the
least items well I guess I'm freaking
phlegm in my throat so in order to do
that after this query we can just go
ahead and add a simple order by
statement now I want to mention this
because a lot of people ask me about
this you know how these are the columns
that were originally in the table ID
name cost seller ID and bids now we also
have this extra column that we named as
item count now this column is a
temporarily temporary column it's kind
of like a nickname a column so can we go
ahead in order by that column even
though it isn't a permanent column well
yes we can whenever we order by item
count it's going to order by that
temporary column in other words the
number of items for our sellers now
since we want to order from highest to
lowest we can't leave it at default
because default is ascending from low to
high we want it from high to low
descending just like that so basically
let me go ahead and run this query and
then I'll talk you guys through it as
you can see it now lists the sellers in
order for the sellers that have the most
items listed due the sellers that have
the least items listed all the way down
to 3 so basically even though this is a
really long and probably confusing query
at first glance I'm going to tell you
guys basically you can read it in plain
English it's like it's saying this take
a deep breath give me the cellar ID in
the number of items from each seller who
listed three or more items and arrange
them from highest to lowest
so again if you just read it in plain
English it's going to make a lot more
sense and that's typically what I do
whenever I come into a database for the
first time that someone else was working
on
I just try to read it like playing
English again each of these keywords not
only the MySQL but also the column names
the written in plain English so
therefore you can just say okay give me
the seller ID and number of items from
each seller who listed three or more
items and then once you have that
information just arrange them from
highest to lowest simple enough so
hopefully you can understand how to read
complex queries by converting that to
plain English but anyways that's all I
have for you guys for the story probably
brain overload but anyways thank you
guys for watching don't forget to
subscribe and in the next tutorial I
promise we're done with all this crap
it's going to get a whole lot easier so
thank you guys for watching and I'll see
you then