MySQL Database Tutorial - 18 - More on Aggregate Functions Video Lecture | MySQL (Sequential Query Language) Database Tutorial - Software Development

alright guys welcome to lesson number 18
and now that we understand functions and
aggregate functions I want to show you
guys how you are typically gonna use
them in a useful manner because if we go
ahead and we look at like the items
table yeah we can go ahead and figure
out the average of all of these or we
can add up all the bids but that isn't
really useful I mean it's kind of a neat
bit of neat piece of information but
that's not something we would actually
use when we were making a website like
eBay what we would typically do is we
would want to find out not the price of
all of the items on the website but
maybe the average price of a single
sellers items or maybe how many items
did I don't know user number eleven half
for sale one two three so let me go
ahead and show you guys how to make
aggregate functions for a single person
or for a single parameter so let's go
ahead and let me think of a real life
problem say we wanted to figure out how
many items seller number six was selling
and by the way seller ID these are just
like different sellers so let's say that
my ID number is number six and I'm
selling a microwave 48 boxes of frogs in
shampooing which I had someone else fill
this out but I guess I'm selling shampoo
maybe it's the brand shampooing I don't
know but anyways I'm selling three
different items so let's go ahead and
figure out how to calculate that so
select now the next aggregate function I
want to talk to you guys about is called
account what count does is you give it a
parameter and that counts all of the
rows in the column for example if you
were just to count the name in from
items it would give you like a hundred
items because that's how many items are
listed on that website however if you
only wanted to count my items you would
add a clause after this for example
where seller underscore ID equals six so
remember my seller ID is equal to six so
this is only
gonna say ok count the number of items
where I am the seller so now if we go
ahead and hit go check it out
now it comes up with that answer three
items because what was I selling crap I
forgot already I remember two of them
like a box of frogs in shampoo what was
the other one oh yeah a microwave box of
frogs and shampoo so basically you could
go ahead and count anything you can go
ahead and count the name or count the
cost it doesn't matter what you count
but as long as you have that Clause
let's see if I nope
copy the wrong thing so as long as you
have that Clause count rose from the
items where seller ID equals six now
what if you wanted to say okay I wonder
what the average price of all of my
items are well we can do that as well
select average cost from items now if we
run this it's gonna go ahead and figure
out the average cost for all of the
items on the entire website but I only
want to get the average cost of my items
so what we do is we would add a where
clause seller underscore ID equals six
now we go ahead and run that and check
it out the average price of my items my
microwave box of frogs and my shampoo if
you add them together and get the
average it's around 80 bucks
pretty cool huh so that is you know a
more useful piece of information maybe
you wanted to give everyone their own
profile and at the top you would have
their statistics you would run a query
like that and show their statistics
ready to top the webpage so now that I
got that taken care if I want to show
you guys one more thing and that is how
to run several aggregate functions at
once now what we can do is we can just
go ahead and run each thing then put a
semicolon at the end run a query again
put a semicolon at the end but I want to
show you guys a shortcut in order to run
several aggregate functions at once
let's go ahead and tie everything we
learn together so far and
let's see let's say we were okay
we know my items already I'm seller ID
number six let's get another good seller
sort by seller ID okay number twelve has
a bunch of items a hairdryer or
Playstation a used car a bikini an air
conditioner so I'm guessing that they
live in Florida with those things but
anyways that's what we're gonna be uh
you know running our data on so first of
all let's go ahead and count all of
their items so in order to do that we
can just go ahead and select count now
again since the rows don't really matter
or what column you want to count because
any column it's gonna come up with the
same number which is like five or six
whatever it was so I'm just going to go
ahead and hit count all as item
underscore counts now remember whenever
I hit as since we were creating our own
custom columns what we're gonna do is
I'm just gonna name it item count and
therefore you're later on it's easier to
see what because I'm gonna be doing a
bunch of different jobs or functions and
it's easier to see if I give each one a
custom name what function I ran now
another function I wanted to talk to you
guys about is Max and min mi M whenever
you run this max it's gonna get the
highest value so if you had three
different items one was ten dollars one
is twenty dollars one was thirty dollars
the max would be thirty in the min would
be ten
so again Max is the highest value of
that list and min is the lowest value so
let's figure out which item cost the
most so max cost of course because we're
working with price and we'll just name
this as Max so whenever we see it we'll
just name it as Max that's what I just
said just wanted to say it twice so now
we already know average let's go ahead
and get the average price of their items
average cost and I'll just name this as
average from items now again if we run
this query right now what it's going to
do is it's gonna get it's gonna count
all of the items get the maximum cost
and get the average
cost for the entire website but I only
want to figure out that the item count
in the max in the average price where
seller ID equals 12 so that's what I'm
gonna do go ahead and run this and check
it out
so again seller 12 who is will say he's
my friend named Tony he's selling five
items his maximum item is selling for
$5,700 that must have been the bikini
and the average price for his items were
fourteen hundred and twenty one and
about a quarter so now if we go over in
the items table and verify this we'll
just make sure that the max check it out
a used-car for fifty seven hundred
dollars and fifty cents so that is
indeed has maximum priced item so there
you go that is how you can run several
aggregate functions at once again the
easiest way is just to put each on a new
line and then make sure to give it a
nickname or name and add something
because whenever you're figuring it out
later on it's easier to go back and look
oh yeah that's the max or that's the
average so that's your little tip and
there you go so guys take a deep breath
you are done with functions and
aggregate functions thank you guys for
watching don't forget subscribe and I'll
see you guys in the next video