Welcome back, everyone.
We've got a very important section here.
We're talking about aggregate functions.
Essentially, we're going to learn about functions that can operate on multiple rows or multiple pieces
of data at once to tell us the min or the max or the average or the sum or the count.
And we're going to start with count.
So the count function is one of the builtin functions we get in my SQL in the same way that we had can
cat and upper and replace and all those.
We don't have to do anything fancy to have access to those.
It's the same concept with count we automatically have access to it.
It's part of the syntax and we would use it in situations like if I wanted to find out how many books
are in the database, that's a very simple one, especially because we know there's like what, 19 or
20?
There's not very many.
We can find it out though, using the count function, if you ever see count and then in parentheses
Star this is telling my SQL.
We want to count the rows that we get back from this query.
So think of this as select the number of rows from books.
Let's try that and see what we get.
Select not star, but count star.
From books.
And we get 19.
So 19 rows in our books database.
Now, it's important to know that this count function doesn't really play nice with other things.
For example, what if I did select title comma, author, f name, comma, count star from books?
What will our results look like?
They don't really look like anything because when we do select count from books, it's boiling the whole
result down into a single value.
But when we do select title and author F name, that's asking for a bunch of rows.
So we're asking for two things that don't work together, at least not easily.
There is a setting we can change.
It tells you about it here in this error message.
It says in your aggregated query this expression number one called title right there is incompatible
with SQL mode only for group.
Don't worry about it.
Really.
It's not a big deal.
But my point is that you don't often even want to do something like count all the rows and at the same
time select title.
But if you wanted to, it doesn't play nicely, at least not with modern SQL.
You have to change some settings if you want to have that behavior.
Okay, so how about if I want to count the number of author first names?
Well, instead of doing Count Star, Count Star is going to select all the rows.
Count author F name will count all of the first names or all the values in that column.
So select count author F name from books, and we still get 19.
So what is that counting?
It's counting every time that a value is present in an author f name in that column.
So all of our author f names are there.
I don't think we have any null, but if I were to insert one into books, insert into books is anything
required.
I think it can just be empty values empty.
I'll do it twice.
Now what we have, if I do a select count star.
This again counts the number of rows from books.
There's 21 total rows.
But if I count the author f names, there's only 19.
Because two of those rows are empty and there is no author f name, they have null in them.
So that could be useful if you just want to know how many times do we even have a value for author f
name?
But more often than not, what we probably want is to know how many distinct author first names are
there.
Maybe not, but at least that's what my slide says.
That's what we want to find out.
How many distinct or unique author first names.
And the way that we do this is we actually pass distinct into the parentheses.
So I want you to count the distinct first names.
So let's try that.
Instead of counting all of them, count the distinct first names and there's only 12.
And that makes sense because we know that there's multiple.
David Rose and Raymond Rose and Neil and Dave.
So when we get rid of those duplicates and we only count the unique names, it's Jhumpa, Neal and then
Dave, Michael, Patti and so on.
These multiple days are only counted one time as an example, so we could do the same thing.
Let's see how many years we had where books were released.
So I'll just start with released year from books.
And I know that there's some duplicates, so let's instead count the distinct released year values.
And there are 16 total, distinct released years because I know at least 2003 shows up twice and I think
2000 something else is in here.
2001 is in here twice right there.
So not a huge impact.
Not that many duplicates, but we're getting the total number of distinct released year values.
So remember, the role of that Distinct is important.
This is valid code.
This tells you how many values are present in the author f name column.
They could be null so they wouldn't be included if they are null.
Whereas here this tells us how many unique values there are for author f name.
So this sort of thing can be really useful.
Let's do one or two more examples here.
We're counting the distinct last names in our dataset, so select count distinct author l name from
books.
And there's 11 distinct other last names because there's Harris in there twice.
I believe there's two different Harris's Frieda Harris and Dan Harris, I think.
And then how about this?
How would we find out?
Find out how many titles contain the word the.
Well, I have the answer.
But let's think about it for a moment.
We know how to select the titles, select title where, and then we would do title like if we want it
to contain the word the and then we'll just have our wild card percent the percent.
And I have to say from books, of course, somehow I forgot that.
Like this.
The namesake, a hologram for the king, the Circle, blah, blah, blah.
Consider the lobster Lincoln in the bardo so we get the correct results.
But I want to count how many rows are here.
So how do I do that?
How do I count how many times or how many rows we got back?
It's actually the first thing we saw in this video.
Instead of selecting title, we'll select count.
Star Count Starr means count the rose and it tells us six.
And that's correct.
Right.
One, two, three, four, five, six, six results from books that had a title that contains the.
All right.
So there's a bit more to learn about count, but we kind of have to learn a whole other topic next which
is grouped by.
Really important stuff.