I'll write to you then.
Welcome back everyone.
In this section we're kind of continuing with the trend of focusing on selecting our data on reading
it if we're going back to crud in the last section.
We saw things like sorting with order by using limit and so on.
In this section we're going to focus on what are called aggregate functions.
And basically these are built in functions in my.
Well that will allow us to aggregate or combine data to get meaning out of it.
So by the end we'll be able to do things like find the average page link of all of our books or find
the average page length for each author.
So you know let's say we'd be able to tell that someone like Carver who writes short stories maybe has
shorter books compared to David Foster Wallace and that's just average but we'll see how to do other
things like count some men max and a bunch of others.
And they're known as aggregate functions because they work on aggregated data.
So yeah let's get started.
It's time to learn some more stuff and it will definitely 100 percent be the most fun thing that you've
ever done.
Let's get going.
The first thing we're going to take a look at the first one of these aggregate functions is called count.
And it does pretty much what you would expect at least in the way that I'm going to show it to you.
Now we're going to revisit count in the next video when we introduce what's called Group By.
But for now we're just focusing on count on its own.
And what it will do is count whatever you tell it to count.
So here's an example.
How many books are in our database.
If we want to do that right now you could do a select start from books and manually count which technically
would mean that would work.
But if we have you know Amazon's database that has millions of books that would be a lot of work that
would take at least a couple of thousand Mechanical Turk to do so.
That's not ideal.
Instead we can use count and it just looks like this.
Select count star from books.
So let's try it and then we'll discuss it a bit more.
So here I am.
I'm using the same database just double checking bookshop so we can do a select star from books.
And like I said we could go through an account and you can just rely on the IDs of course because we
could have deleted some.
So just that the highest number of the ID is not a reliable way of knowing how many books are in there.
Because when you delete something those IDs don't change they don't shift down.
So instead we do a select count and we'll put start here which basically will count every row the entire
row so select count star from books and we get 19.
So that tells us there are 19 books and we could verify that.
You know let me just speed this up as I count 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19.
All right so there were 19 books so we can trust this count.
That's right and that's one way of using counters to check how many things are in table in general a
more specific way of using it would be to do something like this.
How many author first names are in our database and it's not 19 we know that now.
Because if we do a select author name from books you know we have some authors who are in here multiple
times Neal game and it's in there twice.
So it's going to count them twice.
It's just going to count everything here.
So if we did this select count author name from books is it really going to be correct.
And the answer is no as I just said.
But let's just try it.
It's like the Count of author L name from books or we're doing actually FNMA or.
And it tells us there are 19 author names and that's technically true there are 19 f names in here.
I want to know how many unique first names are in there.
Unfortunately there's a way of doing that and it relies on distinct which we've already seen.
So we just put distinct inside if that counts.
So select count distinct author F. name from books.
Let's try it.
Select count distinct author F. name from books and now it tells us 12 and I won't make you sit through
me counting it.
But there are indeed 12 first names that we have in our database.
So instead of 19 it's now decided.
OK.
There's only one here and there's one here and there's one here.
So that's the basics of using count along with using count with distinct.
Now I do want to show you a bit of an edge case here.
Let's say I want to know how many unique authors are in my database.
So that means first name and last name.
So if we do our select count and let's just you author last name first from books.
We get 19 as you would expect there's 19 rows.
Every row has a last name.
But we know that there are some duplicated last names and if we just look at those just do this very
quickly it's the same problem that we had a first name right.
We had Foster Wallace Dave Eggers and we have Geymann So if we use that distinct select account distinct
last names maybe you can see where I'm going here.
I hit enter.
It tells us there's 11 distinct last names which is true.
There are 11 distinct last names but that doesn't mean there are 11 distinct authors because if we go
and do a select author f name comma author L name.
So if we look at both first name and last name from books remember that we have Dan Harris and Frieda
Harris.
So when we're selecting distinct last names it treats this as the same because Harris is the same as
Harris.
So if we wanted to know how many distinct authors were in there what we could do is to author L name
comma author name and now it's only going to return distinct roads where first name and last name or
last name and first name are distinct and that gives us 12 which in our case is the same as just doing
this right here as we just saw if we only to author FNM but that's because we don't have any duplicated
names or any duplicated first names in there.
We have David Foster Wallace but we don't have I don't know David Johnson or something.
So this would be problematic if we had multiple Davids.
So then we could use this where we're doing distinct author I'll name an author name together and the
last thing we'll do is this question here.
How many titles contain the string.
The Th.
So if you'd like to make this a little bit of a mini exercise go ahead and try it.
How many titles contain the in the title.
How many books title contains the up and the answer is that we need to use like alongside with count.
So let's do it now.
We're going to do a select and let's just start by selecting the book's title from books where the title
like.
And then this is where we need those wild cards.
Th e we need a wildcard on either side.
All right and here we go.
The Namesake the king circle the amazing the lobster the bardo.
Great book by the way.
So there are six of them here right.
Yes.
So if we wanted to do that with our code dynamically not by counting ourselves you just replace that
with a count.
So let's count from books retitle is like the.
And it tells us there are six.
All right.
So I just want to show you you can combine it just like you can with any other of the things that we've
seen to you know substring or concat or upper.
They work the same way.
In this case we're just selecting count.
All right so that's count.
Next up we're going to go a little bit off the rails and talk about something called group by Brace
yourself.
It's it's refreshing.
Let's put it that way.