All righty, 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 SQL 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 length of all of our books or
find the average page length for each author.
So 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% 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 once we introduce what's called Group Buy.
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 wanted to do that right now, you could do a select star from books and manually count, which
technically would I mean, that would work.
But if we have 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 Turks 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 and count.
And you can't just rely on the IDs of course, because we could have deleted some.
So just 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 Star 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.
Let me just speed this up as I count.
One, two, three, four, five, six, seven, eight, nine, ten, 11, 12, 13, 14, 15, 16, 17,
18, 19.
All right.
So there were 19 books, so we can trust this count.
It's right.
And that's one way of using count just to check how many things are in the 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 f name from books, you know, we have some authors
who are in here multiple times.
Neil Gaiman is 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.
Select the count of author l name from books or we're doing actually f name, aren't we?
And it tells us there are 19 author f 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.
And fortunately there's a way of doing that and it relies on distinct, which we've already seen.
So we just put distinct inside of that count.
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/1 names
that we have in our database.
So instead of 19, it's now decided, okay, 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 wanted 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 do 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, I'll just do this very quickly.
It's the same problem that we had with first name, right?
We had Foster Wallace, we have Eggers and we have gay men.
So if we use that distinct.
Select Count, distinct last names.
Maybe you can see where I'm going here.
If I hit enter, it tells us there's 11 distinct author 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 do author l name,
comma, author f name.
And now it's only going to return distinct rows 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 do author f name.
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 l name and author f name together.
And the last thing we'll do is this question here.
How many titles contain the the string, the E.
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 or how many books title contains the?
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 books, the title from books where
title like.
And then this is where we need those wildcards t h e we need a wild card on either side.
All right.
And here we go.
We've got the namesake, the king, the 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, we would just replace
that with a count, select count from books where title 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.
So substring or cat or upper, they work the same way.
In this case, we're just selecting count.
All right, so let's count.
Next step, 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.