[SOUND] Let's talk numbers.

In this stage, we'll cover some new functions, keywords, and

operators that will help us generate reports and

answer important questions such as, how many books did we sell last month?

How much revenue was generated last week, or what's the average review for

one of our products?

The first function we're going to take a look at is count.

It can be used in many different ways.

Some common usages are counting all the rows in a table.

For example, how many uses does our service have?

Counting the number of results in a query.

For example, how many products cost less than 9.99?

Counting the unique or distinct entries in a table.

For example, how many distinct genres do we have in the movies table?

Finally, you can count aggregates or collections of things.

For example, how many movies do we have in each genre?

Let's see COUNT in action.

Here we have a SELECT statement that selects all uses in the database.

To get the total number of users, I could order by ID in a descending order

and limit it to only one row to be returned.

Getting the ID could possibly reveal the number of users in the database.

However, what it does actually reveal is that,

is the number of users that's ever been.

Imagine any number of users deleted their accounts and

their records were removed from the database.

This number isn't reflective of the current state of the database.

This is why we'd use count,

to get the current number of records in the customers table.

We can add the count function around the asterisks and run the query.

Now, this is the correct number.

You may be wondering why I'd use a start rather then a specific column?

Firstly, it's really quick to type.

I could include something like ID and Run, and

I get the same results, but what happens when we use last underscore name?

The number is smaller than our total.

Why is that?

Count actually counts only the rows where values are present.

In other words, it counts all non-null values in a column.

If a record has no value in that column, for

example the last_name column, then it won't be counted.

Using an asterisk guarantees you'll count all rows, regardless of its contents.

Let's ask another question.

How many users have the first_name of Andrew?

When we run this,

we now see that it counts all of the users with the first name of Andrew.

The COUNT function in this instance, is only counting the rows

of the results of the query, not all of the rows in the table.

Let's see another example in another setting.

Let's count all of the products in the clothing category.

Awesome.

I counted them by how many categories of products we have in the database.

First, let's select the category from the products table.

The results show all of the categories of all of the rows in the table.

In other words, there's duplicates.

We can use a keyword to only return unique values and discard any duplicates.

This keyword is distinct.

Now, I can eyeball and count the categories, but

I don't trust my counting over a computer counting.

So we can wrap the distinct category in the count function.

Now this returns the number of categories we have.

Remember counting nulls no values.

So,if you have a category that is set to null on one of your products,

that apps in category won't be included in this count.

We know how many categories are in the table using the distinct keyword and

the count function.

In the next video, we'll take a look at counting groups of gross for

example, how many products are in each category?